package com.mzj.saas.mservice.propertybill.service;

import cn.hutool.core.collection.CollectionUtil;
import com.alibaba.fastjson.JSON;
import com.mzj.saas.commons.*;
import com.mzj.saas.commons.enums.*;
import com.mzj.saas.commons.vo.redis.TokenRedisVo;
import com.mzj.saas.config.sms.SmsConfig;
import com.mzj.saas.mservice.backlog.entity.*;
import com.mzj.saas.mservice.backlog.repository.*;
import com.mzj.saas.mservice.bill.entity.*;
import com.mzj.saas.mservice.bill.repository.*;
import com.mzj.saas.mservice.bill.vo.*;
import com.mzj.saas.mservice.community.entity.Community;
import com.mzj.saas.mservice.community.entity.CommunityBuild;
import com.mzj.saas.mservice.community.entity.CommunityBuildUnit;
import com.mzj.saas.mservice.community.repository.CommunityBuildRepository;
import com.mzj.saas.mservice.community.repository.CommunityBuildUnitRepository;
import com.mzj.saas.mservice.community.repository.CommunityRepository;
import com.mzj.saas.mservice.community.service.AppCommunityService;
import com.mzj.saas.mservice.eligible.entity.DepartmentEnterpriseTagRef;
import com.mzj.saas.mservice.eligible.entity.DepartmentHierarchy;
import com.mzj.saas.mservice.eligible.repository.DepartmentEnterpriseTagRefRepository;
import com.mzj.saas.mservice.eligible.repository.DepartmentHierarchyRepository;
import com.mzj.saas.mservice.house.entity.Cohabit;
import com.mzj.saas.mservice.house.entity.House;
import com.mzj.saas.mservice.house.entity.HouseMatchUser;
import com.mzj.saas.mservice.house.repository.CohabitRepository;
import com.mzj.saas.mservice.house.repository.HouseMatchUserRepository;
import com.mzj.saas.mservice.house.repository.HouseRepository;
import com.mzj.saas.mservice.pay.service.WxPayService;
import com.mzj.saas.mservice.property.entity.*;
import com.mzj.saas.mservice.property.repository.*;
import com.mzj.saas.mservice.propertybill.entity.EstateExpenseBillDetail;
import com.mzj.saas.mservice.propertybill.repository.EstateExpenseBillDetailRepository;
import com.mzj.saas.mservice.propertybill.repository.EstateExpenseCostRepository;
import com.mzj.saas.mservice.propertybill.vo.IncomeAndExpenditureVO;
import com.mzj.saas.mservice.sms.service.SmsService;
import com.mzj.saas.mservice.sys.entity.AppUser;
import com.mzj.saas.mservice.sys.entity.User;
import com.mzj.saas.mservice.sys.repository.AppUserRepository;
import com.mzj.saas.mservice.sys.repository.UserRepository;
import com.mzj.saas.mservice.sys.utils.SqlUtils;
import com.mzj.saas.redis.RedisService;
import org.apache.commons.lang3.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.BeanUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import java.math.BigDecimal;
import java.text.NumberFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.stream.Collectors;

@Service
public class PayEstateBillService {
    private static final Logger LOG = LoggerFactory.getLogger(PayEstateBillService.class);

    private static final String IAE_COUNT = "SELECT COUNT(1) ";
    private static final String IAE_COLUM = "SELECT a.*";
    private static final String ORDER_ORDER = " ORDER BY a.paymentTime DESC";
    private static final String SQL_PAGE = " LIMIT ? OFFSET ?";

    @Autowired
    private RedisService redisService;
    @Autowired
    private JdbcTemplate jdbcTemplate;
    @Autowired
    private CommunityRepository communityRepository;
    @Autowired
    private CommunityBuildRepository communityBuildRepository;
    @Autowired
    private CommunityBuildUnitRepository communityBuildUnitRepository;
    @Autowired
    private EstateExpenseBillDetailRepository estateExpenseBillDetailRepository;
    @Autowired
    private DepartmentHierarchyRepository departmentHierarchyRepository;
    @Autowired
    private UserRepository userRepository;
    @Autowired
    private AppUserRepository appUserRepository;
    @Autowired
    private EstateExpenseBillRepository estateExpenseBillRepository;
    @Autowired
    private HouseExpenseRuleRefRepository houseExpenseRuleRefRepository;
    @Autowired
    private HouseExpenseBillRuleRepository houseExpenseBillRuleRepository;
    @Autowired
    private HouseRepository houseRepository;
    @Autowired
    private HouseExpenseRentRepository houseExpenseRentRepository;
    @Autowired
    private HouseExpenseOtherRepository houseExpenseOtherRepository;
    @Autowired
    private HouseExpenseHouseRepository houseExpenseHouseRepository;
    @Autowired
    private EstateExpenseCostRepository estateExpenseCostRepository;
    @Autowired
    private HouseExpenseWaterRepository houseExpenseWaterRepository;
    @Autowired
    private HouseExpenseElectricRepository houseExpenseElectricRepository;
    @Autowired
    private HouseExpenseGasRepository houseExpenseGasRepository;
    @Autowired
    private AppCommunityService appCommunityService;
    @Autowired
    private EstateRentBillRepository estateRentBillRepository;
    @Autowired
    private RentBillRepository rentBillRepository;
    @Autowired
    private ExpenseBillRepository expenseBillRepository;
    @Autowired
    private HousePaymentOrderRelationRepository housePaymentOrderRelationRepository;
    @Autowired
    private HousePaymentOrderRepository housePaymentOrderRepository;
    @Autowired
    private EstatePaymentOrderRelationRepository estatePaymentOrderRelationRepository;
    @Autowired
    private EstatePaymentOrderRepository estatePaymentOrderRepository;
    @Autowired
    private HouseWaitPaymentDetailRepository houseWaitPaymentDetailRepository;
    @Autowired
    private CommunityHouseUserRepository communityHouseUserRepository;
    @Autowired
    private BacklogItemRepository backlogItemRepository;
    @Autowired
    private BacklogItemContractBillRepository backlogItemContractBillRepository;
    @Autowired
    private SmsService smsService;
    @Autowired
    private SmsConfig smsConfig;
    @Autowired
    private HouseMatchUserRepository houseMatchUserRepository;
    @Autowired
    private CohabitRepository cohabitRepository;
    @Autowired
    private BacklogItemHouseOtherBillRepository backlogItemHouseOtherBillRepository;
    @Autowired
    private BacklogItemPropertyRentBillRepository backlogItemPropertyRentBillRepository;
    @Autowired
    private BacklogItemPropertyOtherBillRepository backlogItemPropertyOtherBillRepository;
    @Autowired
    private WithDrawService withDrawService;
    @Autowired
    private AccountManageService accountManageService;
    @Autowired
    private MatchUserHouseRepository matchUserHouseRepository;
    @Autowired
    private MatchUserRepository matchUserRepository;
    @Autowired
    private DepartmentEnterpriseTagRefRepository departmentEnterpriseTagRefRepository;
    @Autowired
    private HouseRemainingRelationRepository houseRemainingRelationRepository;
    @Autowired
    private HouseRemainingRepository houseRemainingRepository;
    @Autowired
    private HouseRemainingRecordRepository houseRemainingRecordRepository;
    @Autowired
    private WxPayService wxPayService;


    /**
     * 账单列表-列表信息显示
     *
     * @param accessToken
     * @param pageNumber
     * @param pageSize
     * @param type        0全部账单 1未审核账单 2未缴费账单 3 已缴费账单
     * @param filter      查询对应权限账单 并更新催缴记录
     */
    public ResultBean<Object> billListShow(String accessToken, Integer pageNumber, Integer pageSize, Integer type, Long collectionOrg, BillSearchRequestVO filter) {
        ResultBean<Object> result = new ResultBean<>();
        result.setMsg(StatusCode.SUCCESS_CODE_10000.getErrorMsg());
        result.setCode(StatusCode.SUCCESS_CODE_10000.getErrorCode());
        PaymentOrderApiData<Map<String, Object>> apiData = new PaymentOrderApiData<>();
        TokenRedisVo tokenRedisVo = redisService.findTokenVo(accessToken);
        try {
            // 校验登录信息
            if (tokenRedisVo == null) {
                result.setCode(StatusCode.ERROR_CODE_40001.getErrorCode());
                result.setMsg(StatusCode.ERROR_CODE_40001.getErrorMsg());
                return result;
            }
            Long enterpriseId = tokenRedisVo.getEnterpriseId();
            filter.setOrgId(enterpriseId);
            // 租金
            String rentSql = "SELECT  b.`id`,b.`house_id` AS houseId,b.`community_id` AS communityId,a1.name as name1,a2.name as name2,b.`building_num` AS buildingNum,\n" +
                    "b.`unit`,b.`room_number` AS roomNumber,b.`org_id` AS orgId,b.`match_user_id` AS matchUserId,if(b.`charge_type`=1,8,9) AS chargeType,\n" +
                    "b.`measure_unit` AS measureUnit,b.`cost`, b.`start_time`  AS startTime, b.`end_time` AS endTime, b.`audit_status` AS auditStatus,b.`payment_status`  AS paymentStatus,\n" +
                    "b.`payment_time` AS paymentTime,b.`payment_channel` AS paymentChannel,null AS operatorId,null AS lastUpdateTime,b.`create_time` AS createTime,\n" +
                    "b.should_pay_time AS shouldPayTime,2 as houseType  ,\n" +
                    "(select i.create_time  from  saas_backlog_item_bill ib   \n" +
                    "LEFT JOIN  `saas_backlog_item` i  ON i.`id` = ib.backlog_item_id   \n" +
                    "where ib.`bill_id` = b.id  GROUP BY i.create_time DESC LIMIT 1) AS  itemTime ,\n" +
                    "IFNULL(u.name,ui.name) as name,\n" +
                    "IFNULL(u.mobile,ui.phone) as phone,\n" +
                    "br.collection_org as collectionOrg , b.adjust_status as adjustStatus ,b.surplus_cost as surplusCost ,br.violate_status as violateStatus , br.violate_ratio as violateRatio, muh.company_name as orgMatchUserName  \n" +
                    "FROM `saas_estate_rent_bill` b \n" +
                    "LEFT JOIN  `saas_app_user` a1  ON a1.`id` = b.match_user_id \n" +
                    "LEFT JOIN  `saas_app_user` a2  ON a2.`enterprise_user_id` = b.match_user_id\n" +
                    "LEFT JOIN  `saas_community_house_user` u  ON u.`community_house_id` = b.house_id and u.status=1 and u.type=1\n" +
                    "LEFT JOIN  (select a.house_id ,b.name ,c.phone from  saas_house_match_user a \n" +
                    "LEFT JOIN saas_house_eligible_apply b on b.id = a.apply_id\n" +
                    "LEFT JOIN saas_app_user c on c.id = a.match_user_id \n" +
                    "where  a.status=1 ) ui  ON ui.`house_id` = b.house_id " +
                    "LEFT JOIN saas_house_expense_bill_rule br ON b.rule_id = br.id " +
                    "LEFT JOIN m_match_user_house muh on b.house_id = muh.house_id";


            // 账单
            String billSql = "SELECT  b.`id`,b.`house_id` AS houseId,b.`community_id` AS communityId,a1.name as name1,a2.name as name2,b.`building_num` AS buildingNum,b.`unit`,\n" +
                    "b.`room_number` AS roomNumber,b.`org_id` AS orgId,b.`match_user_id` AS matchUserId,b.`charge_type` AS chargeType,\n" +
                    "b.`measure_unit` AS measureUnit,b.`cost`, b.`start_time`  AS startTime, b.`end_time` AS endTime, b.`audit_status` AS auditStatus,\n" +
                    "b.`payment_status`  AS paymentStatus,b.`payment_time` AS paymentTime,b.`payment_channel` AS paymentChannel,\n" +
                    "b.`operator_id` AS operatorId,b.`last_update_time` AS lastUpdateTime,b.`create_time` AS createTime,b.should_pay_time AS shouldPayTime,2 as houseType ,(select i.create_time  from  saas_backlog_item_bill ib   \n" +
                    "LEFT JOIN  `saas_backlog_item` i  ON i.`id` = ib.backlog_item_id   \n" +
                    "where ib.`bill_id` = b.id  GROUP BY i.create_time DESC LIMIT 1 ) AS  itemTime  ,\n" +
                    "IFNULL(u.name,ui.name) as name,\n" +
                    "IFNULL(u.mobile,ui.phone) as phone,\n" +
                    "br.collection_org as collectionOrg , b.adjust_status as adjustStatus ,b.surplus_cost as surplusCost,br.violate_status as violateStatus , br.violate_ratio as violateRatio, muh.company_name as orgMatchUserName \n " +
                    "FROM `saas_estate_expense_bill` b \n" +
                    "LEFT JOIN  `saas_app_user` a1  ON a1.`id` = b.match_user_id \n" +
                    "LEFT JOIN  `saas_app_user` a2  ON a2.`enterprise_user_id` = b.match_user_id\n" +
                    "LEFT JOIN  `saas_community_house_user` u  ON u.`community_house_id` = b.house_id and u.status=1 and u.type=1\n" +
                    "LEFT JOIN  (select a.house_id ,b.name ,c.phone from  saas_house_match_user a \n" +
                    "LEFT JOIN saas_house_eligible_apply b on b.id = a.apply_id\n" +
                    "LEFT JOIN saas_app_user c on c.id = a.match_user_id \n" +
                    "where  a.status=1 ) ui  ON ui.`house_id` = b.house_id " +
                    "LEFT JOIN saas_house_expense_bill_rule br ON b.rule_id = br.id " +
                    "LEFT JOIN m_match_user_house muh on b.house_id = muh.house_id";
            /*String rentSql2 = "SELECT  b.`id`,b.`house_id` AS houseId,b.`community_id` AS communityId,a1.name as name1,a2.name as name2,b.`building_num` AS buildingNum,b.`unit`,"
                    + "b.`room_number` AS roomNumber,h.house_property_id AS orgId,b.`match_user_id` AS matchUserId,if(b.`charge_type`=1,8,9) AS chargeType,"
                    + "b.`measure_unit` AS measureUnit,b.`cost`, b.`start_time`  AS startTime, b.`end_time` AS endTime, b.`audit_status` AS auditStatus,"
                    + "b.`payment_status`  AS paymentStatus,b.`payment_time` AS paymentTime,b.`payment_channel` AS paymentChannel,"
                    + "null AS operatorId,null AS lastUpdateTime,b.`create_time` AS createTime,b.should_pay_time AS shouldPayTime,1 as houseType  "
                    + "FROM `saas_rent_bill` b "
                    + "LEFT JOIN  `saas_house` h  ON h.id = b.house_id "
                    + "LEFT JOIN  `saas_app_user` a1  ON a1.`id` = b.match_user_id "
                    + "LEFT JOIN  `saas_app_user` a2  ON a2.`enterprise_user_id` = b.match_user_id ";

            String billSq2 = "SELECT  b.`id`,b.`house_id` AS houseId,b.`community_id` AS communityId,a1.name as name1,a2.name as name2,b.`building_num` AS buildingNum,b.`unit`,"
                    + "b.`room_number` AS roomNumber,h.house_property_id AS orgId,b.`match_user_id` AS matchUserId,b.`charge_type` AS chargeType,"
                    + "b.`measure_unit` AS measureUnit,b.`cost`, b.`start_time`  AS startTime, b.`end_time` AS endTime, b.`audit_status` AS auditStatus,"
                    + "b.`payment_status`  AS paymentStatus,b.`payment_time` AS paymentTime,b.`payment_channel` AS paymentChannel,"
                    + "b.`operator_id` AS operatorId,b.`last_update_time` AS lastUpdateTime,b.`create_time` AS createTime,b.should_pay_time AS shouldPayTime,1 as houseType  "
                    + "FROM `saas_expense_bill` b "
                    + "LEFT JOIN  `saas_house` h  ON h.id = b.house_id "
                    + "LEFT JOIN  `saas_app_user` a1  ON a1.`id` = b.match_user_id "
                    + "LEFT JOIN  `saas_app_user` a2  ON a2.`enterprise_user_id` = b.match_user_id ";*/

            billSql = " select v.* from ( " + rentSql + " UNION ALL " + billSql + " ) v";

            List<Object> args = new ArrayList<>();
            StringBuilder where = new StringBuilder(" WHERE 1 = 1 \n");

            // 判断用户所属机构是否设置层级架构
            DepartmentHierarchy hierarchy = departmentHierarchyRepository.findByOrgIdAndStatus(enterpriseId, 1);
            if (hierarchy == null) {
                return ResultBean.failedResultWithMsg("用户所属机构未设置层级架构");
            }
            // 查询出企业的标签
            Long tagId = hierarchy.getId();
            List<DepartmentEnterpriseTagRef> refs = departmentEnterpriseTagRefRepository.findByDepRefId(tagId);
            List<Long> enterpriseTagIds = refs.stream().map(DepartmentEnterpriseTagRef::getTagId).distinct().collect(Collectors.toList());// 用户企业标签


            // 权限过滤
            List<Map<String, Object>> communityList = appCommunityService.findCommunity(accessToken).getResultData();
            if (communityList != null && !communityList.isEmpty()) {
                where.append(" and v.communityId in " + SqlUtils.foreachIn(communityList.size()));
                List<String> communityIds = communityList.stream().map(map -> {
                    String communityId = map.get("communityId").toString();
                    return communityId;
                }).collect(Collectors.toList());
                args.addAll(communityIds);
            } else {
                where.append(" and v.communityId in (null) ");
            }

            // 0全部账单 1未审核账单 2未缴费账单 3已缴费账单 4未通过账单
            if (type != 0) {
                switch (type) {
                    case 1:
                        where.append(" AND v.auditStatus=0 ");
                        break;
                    case 2:
                        where.append("  AND v.auditStatus = 1 and v.paymentStatus in (0,2) ");
                        break;
                    case 3:
                        where.append("  AND v.paymentStatus=1 ");
                        break;
                    case 4:
                        where.append("  AND v.auditStatus = 2  ");
                        break;
                }
            }

            String offsets = "   LIMIT ? OFFSET ? ";

            // 条件查询
            if (StringUtils.isNotBlank(filter.getHouseId())) {
                where.append("  AND  v.`houseId` = ?  \n");
                args.add(filter.getHouseId());
            }
            if (StringUtils.isNotBlank(filter.getCommunityId())) {
                where.append("  AND  v.`communityId` = ?  \n");
                args.add(filter.getCommunityId());
            }
            if (filter.getBuildingNum() != null) {
                where.append("  AND  v.`buildingNum` =  ?  \n");
                args.add(filter.getBuildingNum());
            }
            if (filter.getUnit() != null) {
                where.append("  AND  v.`unit` =  ?  \n");
                args.add(filter.getUnit());
            }
            if (StringUtils.isNotBlank(filter.getRoomNumber())) {
                where.append("  AND  v.`roomNumber` = ?  \n");
                args.add(filter.getRoomNumber());
            }
            if (filter.getOrgId() != null) {
                where.append("  AND  v.`orgId` =  ?  \n");
                args.add(filter.getOrgId());
            }
//            if (StringUtils.isNotBlank(filter.getMatchUserName())) {
//                where.append("  AND  (v.`name1` like ? or v.`name2` like ?)  \n");
//                args.add("%" + filter.getMatchUserName() + "%");
//                args.add("%" + filter.getMatchUserName() + "%");
//            }

            if (StringUtils.isNotBlank(filter.getMatchUserName())) {
                where.append("  AND  IFNULL(v.`name`,v.orgMatchUserName) like ?   \n");
                args.add("%" + filter.getMatchUserName() + "%");
            }

            if (filter.getChargeType() != null) {
                where.append("  AND  v.`chargeType` =  ?  \n");
                args.add(filter.getChargeType());
            }
            if (filter.getPaymentStatus() != null) {
                where.append("  AND  v.`paymentStatus` =  ?  \n");
                args.add(filter.getPaymentStatus());
            }

            if (filter.getPaymentChannel() != null) {
                where.append("  AND  v.`paymentChannel` =  ?  \n");
                args.add(filter.getPaymentChannel());
            }

            // 非政府机构
            if (!enterpriseTagIds.contains(3L)) {
                if (collectionOrg != null) {
                    where.append("  AND  v.`collectionOrg` =  ?  \n");
                    args.add(collectionOrg);
                }
            }
            List<Map<String, Object>> peopleList = new ArrayList<>();
            // 查询总记录数
            String count = "select count(1) FROM (" + billSql + where + ") c  ";
            Long total = jdbcTemplate.queryForObject(count, Long.class, args.toArray());
            String countCost = "";
            if (type != 3) {
                countCost = "select IFNULL(SUM(c.surplusCost),0) as totalAmount  FROM (" + billSql + where + ") c  ";
            } else {
                countCost = "select IFNULL(SUM(c.cost),0) as totalAmount  FROM (" + billSql + where + ") c  ";
            }
            Long totalAmount = jdbcTemplate.queryForObject(countCost, Long.class, args.toArray());
            // LOG.info("SQL :" + billSql + where);
            if (total == 0) {
                result.setCode(StatusCode.SUCCESS_CODE_10000.getErrorCode());
                result.setMsg(StatusCode.SUCCESS_CODE_10000.getErrorMsg());
                apiData.setData(peopleList);
                apiData.setTotal(total);
                apiData.setTotalAmount(ConvertUtils.fenToYuan(totalAmount.toString()));
                result.setResultData(apiData);
                return result;
            }

            // 查询分页数据
            where.append("  ORDER BY ").append(" v.createTime desc,v.id desc ").append(" \n").append(offsets);
            args.add(pageSize);
            args.add(pageNumber * pageSize);

            peopleList = jdbcTemplate.queryForList(billSql + where, args.toArray());

            // 封装账单数据
            for (Map<String, Object> map : peopleList) {
                Long id = Long.valueOf(String.valueOf(map.get("id")));
                String communityId = String.valueOf(map.get("communityId"));
                Long buildingNum = Long.valueOf(String.valueOf(map.get("buildingNum")));
                Long unit = Long.valueOf(String.valueOf(map.get("unit")));
                Long orgId = Long.valueOf(String.valueOf(map.get("orgId")));
                String matchUserId = String.valueOf(map.get("matchUserId"));
                Integer chargeType = Integer.valueOf(String.valueOf(map.get("chargeType")));
                String cost = fenToYuan(String.valueOf(map.get("cost")));
                String orgMatchUserName = String.valueOf(map.get("orgMatchUserName"));
                map.put("cost", cost);
                String surplusCost = fenToYuan(String.valueOf(map.get("surplusCost")));
                map.put("surplusCost", surplusCost);
                Object itemTime = map.get("itemTime");
                if (null == itemTime || StringUtils.isBlank(String.valueOf(itemTime))) {
                    map.put("display", 0);
                } else {
                    map.put("display", judgmentDate(String.valueOf(itemTime)));
                }

                map.put("paymentTime", String.valueOf(map.get("paymentTime")));


                String houseId = String.valueOf(map.get("houseId"));
                if (StringUtils.isNotBlank(matchUserId)) {
                    List<MatchUser> matchUserList = matchUserRepository.findByUserId(matchUserId);
                    if (CollectionUtil.isNotEmpty(matchUserList)) {
                        MatchUser matchUser = matchUserList.get(0);
                        map.put("matchUserName", matchUser.getName());
                        map.put("matchUserPhone", matchUser.getPhone());
                        map.put("name", matchUser.getName());
                    }
                }
                // List<MatchUserHouse> byHouseId = matchUserHouseRepository.findByHouseId(houseId);
                // if (!byHouseId.isEmpty()) {
                //     MatchUserHouse matchUserHouse = byHouseId.get(0);
                //     Integer label = matchUserHouse.getLabel();
                //     if (label == 1) {
                //         //个人承租人
                //
                //     } else {
                //         if (StringUtils.isNotEmpty(matchUserHouse.getMatchUserStockId())) {
                //             Optional<MatchUser> matchUserRepositoryById = matchUserRepository.findById(Long.parseLong(matchUserHouse.getMatchUserStockId()));
                //             if (matchUserRepositoryById.isPresent()) {
                //                 //产权下的用户承租人id
                //                 MatchUser byMatchUserId = matchUserRepositoryById.get();
                //                 map.put("matchUserPhone", byMatchUserId.getPhone());
                //             }
                //         }
                //         map.put("matchUserName", orgMatchUserName);
                //         map.put("name", orgMatchUserName);
                //     }
                // }


                Optional<Community> communityOptional = communityRepository.findById(communityId);
                // 获取小区名称
                if (communityOptional.isPresent()) {
                    Community community = communityOptional.get();
                    map.put("communityName", community.getName());
                }
                // 获取楼栋名称
                Optional<CommunityBuild> communityBuildOptional = communityBuildRepository.findById(buildingNum);
                if (communityBuildOptional.isPresent()) {
                    CommunityBuild communityBuild = communityBuildOptional.get();
                    map.put("buildingNumName", communityBuild.getName());
                }
                // 获取单元名称
                Optional<CommunityBuildUnit> communityBuildUnitOptional = communityBuildUnitRepository.findById(unit);
                if (communityBuildUnitOptional.isPresent()) {
                    CommunityBuildUnit communityBuildUnit = communityBuildUnitOptional.get();
                    map.put("unitName", communityBuildUnit.getName());
                }
                // 获取物业名称
                Optional<House> byId = houseRepository.findById(houseId);
                Long housePropertyId = byId.get().getHousePropertyId();
                DepartmentHierarchy propertyUnit = departmentHierarchyRepository.findByOrgId(housePropertyId);
                if (propertyUnit != null) {
                    map.put("orgName", propertyUnit.getName());
                }
                // 获取承租人名称、手机号
                // User lesseeUser = userRepository.findById(matchUserId);
//                AppUser lesseeUser = appUserRepository.findByIdOrEnterpriseUserId(matchUserId, matchUserId);
//                if (lesseeUser != null) {
//                    map.put("matchUserName", lesseeUser.getName());
//                    map.put("matchUserPhone", lesseeUser.getPhone());
//                    map.put("name", lesseeUser.getName());
//                }

                if (chargeType == 8) {
                    map.put("chargeTypeName", "租金");
                } else if (chargeType == 9) {
                    map.put("chargeTypeName", "押金");
                } else if (chargeType == 10) {
                    map.put("chargeTypeName", "退租");
                }
                Integer houseType = Integer.parseInt(String.valueOf(map.get("houseType")));
                if (houseType == 1) {
                    HouseWaitPaymentDetail expenseBillDetail = houseWaitPaymentDetailRepository.findByExpenseBillIdAndChargeType(id, chargeType);
                    if (expenseBillDetail != null) {
                        map.put("estateExpenseBillDetailId", expenseBillDetail.getId());
                        switch (chargeType) {
                            case 1:
                                map.put("chargeTypeName", "水费");
                                map.put("chargeTypeUnit", fenToYuan(String.valueOf(expenseBillDetail.getWaterUnitPrice())));
                                // map.put("chargeTypeUseNumber", estateExpenseBillDetail.getWaterUsage() + "吨");
                                map.put("startNum", expenseBillDetail.getWaterStartNum());
                                map.put("endNum", expenseBillDetail.getWaterEndNum());
                                map.put("usage", expenseBillDetail.getWaterUsage());
                                break;
                            case 2:
                                map.put("chargeTypeName", "电费");
                                map.put("chargeTypeUnit", fenToYuan(String.valueOf(expenseBillDetail.getElectricUnitPrice())));
                                // map.put("chargeTypeUseNumber", estateExpenseBillDetail.getElectricUsage() + "度");
                                map.put("startNum", expenseBillDetail.getElectricStartNum());
                                map.put("endNum", expenseBillDetail.getElectricEndNum());
                                map.put("usage", expenseBillDetail.getElectricUsage());
                                break;
                            case 3:
                                map.put("chargeTypeName", "物业费");
                                break;
                            case 4:
                                map.put("chargeTypeName", "宽带费");
                                break;
                            case 5:
                                map.put("chargeTypeName", "电视费");
                                break;
                            case 6:
                                map.put("chargeTypeName", "燃气费");
                                map.put("chargeTypeUnit", fenToYuan(String.valueOf(expenseBillDetail.getGasUnitPrice())));
                                map.put("startNum", expenseBillDetail.getGasStartNum());
                                map.put("endNum", expenseBillDetail.getGasEndNum());
                                map.put("usage", expenseBillDetail.getGasUsage());
                                break;
                            case 7:
                                map.put("chargeTypeName", "暖气费");
                                break;
                        }
                    }
                } else {
                    EstateExpenseBillDetail estateExpenseBillDetail = estateExpenseBillDetailRepository.findByExpenseBillIdAndChargeType(id, chargeType);
                    if (estateExpenseBillDetail != null) {
                        map.put("estateExpenseBillDetailId", estateExpenseBillDetail.getId());
                        switch (chargeType) {
                            case 1:
                                map.put("chargeTypeName", "水费");
                                map.put("chargeTypeUnit", fenToYuan(String.valueOf(estateExpenseBillDetail.getWaterUnitPrice())));
                                // map.put("chargeTypeUseNumber", estateExpenseBillDetail.getWaterUsage() + "吨");
                                map.put("startNum", estateExpenseBillDetail.getWaterStartNum());
                                map.put("endNum", estateExpenseBillDetail.getWaterEndNum());
                                map.put("usage", estateExpenseBillDetail.getWaterUsage());
                                break;
                            case 2:
                                map.put("chargeTypeName", "电费");
                                map.put("chargeTypeUnit", fenToYuan(String.valueOf(estateExpenseBillDetail.getElectricUnitPrice())));
                                // map.put("chargeTypeUseNumber", estateExpenseBillDetail.getElectricUsage() + "度");
                                map.put("startNum", estateExpenseBillDetail.getElectricStartNum());
                                map.put("endNum", estateExpenseBillDetail.getElectricEndNum());
                                map.put("usage", estateExpenseBillDetail.getElectricUsage());
                                break;
                            case 3:
                                map.put("chargeTypeName", "物业费");
                                break;
                            case 4:
                                map.put("chargeTypeName", "宽带费");
                                break;
                            case 5:
                                map.put("chargeTypeName", "电视费");
                                break;
                            case 6:
                                map.put("chargeTypeName", "燃气费");
                                map.put("chargeTypeUnit", fenToYuan(String.valueOf(estateExpenseBillDetail.getGasUnitPrice())));
                                map.put("startNum", estateExpenseBillDetail.getGasStartNum());
                                map.put("endNum", estateExpenseBillDetail.getGasEndNum());
                                map.put("usage", estateExpenseBillDetail.getGasUsage());
                                break;
                            case 7:
                                map.put("chargeTypeName", "暖气费");
                                break;
                        }
                    }
                }
            }

            // 同步催缴费间隔状态(24小时内只能催缴一次)
            /*peopleList.forEach(dd -> {
                // 24小时相同订单只能催缴一次
                String value = redisService.gutBillId(dd.get("id") + "_" + dd.get("chargeType"));
                if (StringUtils.isNotBlank(value)) {
                    // 有催缴记录
                    dd.put("worthStatus", 1);
                } else {
                    dd.put("worthStatus", 0);
                }
            });*/

            apiData.setData(peopleList);
            apiData.setTotal(total);
            apiData.setTotalAmount(ConvertUtils.fenToYuan(totalAmount.toString()));
            // LOG.info("SQL :" + billSql + where);
            result.setResultData(apiData);
        } catch (Exception e) {
            LOG.error(e.getMessage(), e);
            result.setCode(StatusCode.ERROR_CODE_10001.getErrorCode());
            result.setMsg(e.getMessage());
        }
        return result;
    }

    /**
     * 判断是否超过24小时
     *
     * @param date1 开始时间
     * @return boolean
     * @throws Exception
     */
    public static Integer judgmentDate(String date1) throws Exception {
        if (StringUtils.isBlank(date1)) {
            return 0;
        }
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

        Date start = sdf.parse(date1);

        Date end = sdf.parse(sdf.format(new Date()));

        long cha = end.getTime() - start.getTime();

        if (cha < 0) {

            return 1;

        }

        double result = cha * 1.0 / (1000 * 60 * 60);

        if (result < 24) {

            return 1;

        } else {

            return 1;

        }

    }

    /**
     * 账单列表-编辑账单
     *
     * @param accessToken
     * @param vo
     * @return 修改账单信息  当前账单起数必须大于前一个订单止数
     */
    public ResultBean<Object> editBill(String accessToken, EditBillRequestVO vo) {
        try {
            TokenRedisVo tokenRedisVo = redisService.findTokenVo(accessToken);
            if (tokenRedisVo == null) {
                return ResultBean.failedResultOfToken();
            }


            if (vo.getId() == null || vo.getBeginNum() == null
                    || vo.getUseNum() == null || vo.getCost() == null) {
                return ResultBean.failedResultOfParamWithMsg("参数错误");
            }

            if (vo.getUseNum() < 0) {
                return ResultBean.failedResultOfParamWithMsg("用量不可为负数");

            }
            if (vo.getHouseType() == null) {
                return ResultBean.failedResultOfParamWithMsg("房屋类型为空");
            }

            if (vo.getHouseType() == 1) { // 公租房
                Optional<ExpenseBill> expenseBillOptional = expenseBillRepository.findById(vo.getId());
                if (!expenseBillOptional.isPresent()) {
                    return ResultBean.failedResultWithMsg("账单信息不存在");
                }
                ExpenseBill expenseBill = expenseBillOptional.get();
                Integer chargeType = expenseBill.getChargeType();

                List<HouseWaitPaymentDetail> billDetails = houseWaitPaymentDetailRepository.findByExpenseBillId(vo.getId());
                if (billDetails.isEmpty()) {
                    return ResultBean.failedResultWithMsg("账单信息不存在");
                }
                HouseWaitPaymentDetail billDetail = billDetails.get(0);

              /*  //获取前一个账单
                String sql = "SELECT d.* FROM saas_expense_bill b  \n" +
                        "JOIN saas_house_wait_payment_detail d ON b.id = d.expense_bill_id \n" +
                        "WHERE b.house_id = ? AND b.charge_type = ? AND b.audit_status = 1  \n" +
                        "AND b.match_user_id IS NOT NULL AND b.id != ? and  b.start_time < ? \n" +
                        "ORDER BY b.create_time DESC LIMIT 1";
                ArrayList<Object> args = new ArrayList<>();
                args.add(expenseBill.getHouseId());
                args.add(expenseBill.getChargeType());
                args.add(expenseBill.getId());
                args.add(expenseBill.getStartTime());
                List<Map<String, Object>> upBills = jdbcTemplate.queryForList(sql, args.toArray());
                if (!upBills.isEmpty()) {
                    //当前账单起数必须大于前一个订单止数
                    Map<String, Object> map = upBills.get(0);
                    Integer endNum = null;
                    switch (chargeType) {
                        case 1:
                            endNum = Integer.parseInt(map.get("water_end_num").toString());
                            break;
                        case 2:
                            endNum = Integer.parseInt(map.get("electric_end_num").toString());
                            break;
                        case 6:
                            endNum = Integer.parseInt(map.get("gas_end_num").toString());
                            break;
                    }
                    if (vo.getBeginNum() <= endNum) {
                        return ResultBean.failedResultWithMsg("账单起数必须大于前一个订单止数");
                    }
                }*/

                expenseBill.setCost(ConvertUtils.yuan2fen(vo.getCost()));
                expenseBillRepository.save(expenseBill);

                // 设置对应账单信息
                switch (chargeType) {
                    case 1:
                        billDetail.setWaterStartNum(vo.getBeginNum());
                        billDetail.setWaterEndNum(vo.getBeginNum() + vo.getUseNum());
                        billDetail.setWaterUsage(vo.getUseNum());
                        break;
                    case 2:
                        billDetail.setElectricStartNum(vo.getBeginNum());
                        billDetail.setElectricEndNum(vo.getBeginNum() + vo.getUseNum());
                        billDetail.setElectricUsage(vo.getUseNum());
                        break;
                    case 6:
                        billDetail.setGasStartNum(vo.getBeginNum());
                        billDetail.setGasEndNum(vo.getBeginNum() + vo.getUseNum());
                        billDetail.setGasUsage(vo.getUseNum());
                        break;
                }
                houseWaitPaymentDetailRepository.save(billDetail);
            }

            if (vo.getHouseType() == 2) { // 房产
                Optional<EstateExpenseBill> estateExpenseBillOptional = estateExpenseBillRepository.findById(vo.getId());
                if (!estateExpenseBillOptional.isPresent()) {
                    return ResultBean.failedResultWithMsg("账单信息不存在");
                }
                EstateExpenseBill estateExpenseBill = estateExpenseBillOptional.get();
                Integer chargeType = estateExpenseBill.getChargeType();

                EstateExpenseBillDetail billDetail = estateExpenseBillDetailRepository.findByExpenseBillId(vo.getId());
                if (billDetail == null) {
                    return ResultBean.failedResultWithMsg("账单信息不存在");
                }

                // 获取前一个账单
                /*String sql = "SELECT d.* FROM saas_estate_expense_bill b  \n" +
                        "JOIN saas_estate_expense_bill_detail d ON b.id = d.expense_bill_id \n" +
                        "WHERE b.house_id = ? AND b.charge_type = ? AND b.audit_status = 1  \n" +
                        "AND b.match_user_id IS NOT NULL AND b.id != ? and  b.start_time < ? \n" +
                        "ORDER BY b.create_time DESC LIMIT 1";
                ArrayList<Object> args = new ArrayList<>();
                args.add(estateExpenseBill.getHouseId());
                args.add(estateExpenseBill.getChargeType());
                args.add(estateExpenseBill.getId());

                args.add(estateExpenseBill.getStartTime());
                List<Map<String, Object>> upBills = jdbcTemplate.queryForList(sql, args.toArray());
                if (!upBills.isEmpty()) {
                    //当前账单起数必须大于前一个订单止数
                    Map<String, Object> map = upBills.get(0);
                    Integer endNum = null;
                    switch (chargeType) {
                        case 1:
                            endNum = Integer.parseInt(map.get("water_end_num").toString());
                            break;
                        case 2:
                            endNum = Integer.parseInt(map.get("electric_end_num").toString());
                            break;
                        case 6:
                            endNum = Integer.parseInt(map.get("gas_end_num").toString());
                            break;
                    }
                    if (vo.getBeginNum() <= endNum) {
                        return ResultBean.failedResultWithMsg("账单起数必须大于前一个订单止数");
                    }
                }*/


                Integer cost = 0;
                // 1水费 2电费 3物业费 4宽带费 5电视费 6煤气费 7暖气费 10退租 11余额)
                switch (chargeType) {
                    case 1:
                        billDetail.setWaterStartNum(vo.getBeginNum());
                        billDetail.setWaterEndNum(vo.getBeginNum() + vo.getUseNum());
                        billDetail.setWaterUsage(vo.getUseNum());
                        cost = billDetail.getWaterUnitPrice() * vo.getUseNum();
                        break;
                    case 2:
                        billDetail.setElectricStartNum(vo.getBeginNum());
                        billDetail.setElectricEndNum(vo.getBeginNum() + vo.getUseNum());
                        billDetail.setElectricUsage(vo.getUseNum());
                        cost = billDetail.getElectricUnitPrice() * vo.getUseNum();
                        break;
                    case 6:
                        billDetail.setGasStartNum(vo.getBeginNum());
                        billDetail.setGasEndNum(vo.getBeginNum() + vo.getUseNum());
                        billDetail.setGasUsage(vo.getUseNum());
                        cost = billDetail.getGasUnitPrice() * vo.getUseNum();
                        break;

                }

                estateExpenseBill.setCost(cost);
                estateExpenseBill.setSurplusCost(cost);
                estateExpenseBillRepository.save(estateExpenseBill);

                estateExpenseBillDetailRepository.save(billDetail);
            }

            return ResultBean.successfulResult(null);
        } catch (Exception e) {
            LOG.error(e.getMessage(), e);
            return ResultBean.failedResultOfException();
        }
    }

    /**
     * 账单列表-审核
     *
     * @param accessToken
     * @param vo          审核公租房和非公租房 租金押金 和其他费用
     */
    @Transactional
    public ResultBean<Object> examineBill(String accessToken, EditBillRequestVO vo) {
        ResultBean<Object> result = new ResultBean<>();
        LOG.info("PayEstateBillService, examineBill, vo={}", JSON.toJSONString(vo));
        result.setMsg(StatusCode.SUCCESS_CODE_10000.getErrorMsg());
        result.setCode(StatusCode.SUCCESS_CODE_10000.getErrorCode());
        TokenRedisVo tokenRedisVo = redisService.findTokenVo(accessToken);
        try {
            // 校验登录信息
            if (tokenRedisVo == null) {
                result.setCode(StatusCode.ERROR_CODE_40001.getErrorCode());
                result.setMsg(StatusCode.ERROR_CODE_40001.getErrorMsg());
                return result;
            }

            // 0账单审核 1缴费凭证审核
            Boolean flag = accountManageService.checkAuditor(accessToken, 0);
            if (!flag) {
                return ResultBean.failedResultWithMsg("审核权限未授权,请联系管理员授权");
            }

            if (vo.getRentIds().isEmpty() && vo.getExpenseIds().isEmpty()
                    && vo.getEstateRentIds().isEmpty() && vo.getEstateExpenseIds().isEmpty()) {
                return ResultBean.failedResultWithMsg("账单id为空");
            }
            if (vo.getAuditStatus() == null) {
                return ResultBean.failedResultWithMsg("审核状态不能为空");
            }

            Date date = new Date();

            // 公租房 租金/押金账单ids
            if (vo.getRentIds() != null && !vo.getRentIds().isEmpty()) {
                List<RentBill> rentBills = rentBillRepository.findByIdIn(vo.getRentIds());
                for (RentBill rentBill : rentBills) {
                    rentBill.setAuditStatus(vo.getAuditStatus());
                    /**
                     * 公租房租金待办事项
                     */
                    rentBillBacklogItem(rentBill.getHouseId(), rentBill.getMatchUserId(), rentBill.getCost(), rentBill.getId(), tokenRedisVo.getEnterpriseUserId(), rentBill.getChargeType());
                }
                rentBillRepository.saveAll(rentBills);
            }

            // 公租房 其他费用账单ids
            if (vo.getExpenseIds() != null && !vo.getExpenseIds().isEmpty()) {
                List<ExpenseBill> expenseBills = expenseBillRepository.findByIdIn(vo.getExpenseIds());
                for (ExpenseBill expenseBill : expenseBills) {
                    List<HouseWaitPaymentDetail> billDetails = houseWaitPaymentDetailRepository.findByExpenseBillId(expenseBill.getId());
                    if (billDetails.isEmpty()) {
                        return ResultBean.failedResultWithMsg("账单数据有误");
                    }
                    HouseWaitPaymentDetail billDetail = billDetails.get(0);

                    Integer chargeType = expenseBill.getChargeType();
                    switch (chargeType) {
                        case 1:
                            if (expenseBill.getCost() == null || billDetail.getWaterStartNum() == null || billDetail.getWaterEndNum() == null) {
                                return ResultBean.failedResultWithMsg("有账单需完善信息，无法审核");
                            }
                            break;
                        case 2:
                            if (expenseBill.getCost() == null || billDetail.getElectricStartNum() == null || billDetail.getElectricEndNum() == null) {
                                return ResultBean.failedResultWithMsg("有账单需完善信息，无法审核");
                            }
                            break;
                        case 6:
                            if (expenseBill.getCost() == null || billDetail.getGasStartNum() == null || billDetail.getGasEndNum() == null) {
                                return ResultBean.failedResultWithMsg("有账单需完善信息，无法审核");
                            }
                            break;
                    }

                    expenseBill.setAuditStatus(vo.getAuditStatus());// 已审核
                    expenseBill.setOperatorId(tokenRedisVo.getId());
                    expenseBill.setLastUpdateTime(date);
                    /**
                     * 公租房其他费用账单待办事项
                     */
                    houseOtherBillItem(expenseBill.getHouseId(), expenseBill.getMatchUserId(), expenseBill.getId(), expenseBill.getChargeType(), expenseBill.getCost(), tokenRedisVo.getEnterpriseUserId());
                }
                expenseBillRepository.saveAll(expenseBills);
            }

            List<Map<String, Object>> bills = new ArrayList<>();
            // 房产租金/押金账单ids
            if (vo.getEstateRentIds() != null && !vo.getEstateRentIds().isEmpty()) {
                List<EstateRentBill> rentBills = estateRentBillRepository.findByIdIn(vo.getEstateRentIds());
                for (EstateRentBill rentBill : rentBills) {
                    rentBill.setAuditStatus(vo.getAuditStatus());

                    HouseExpenseRuleRef ref = houseExpenseRuleRefRepository.findByRuleIdAndHouseId(rentBill.getRuleId(), rentBill.getHouseId());
                    if (ref != null) {
                        ref.setAuditStatus(vo.getAuditStatus());
                        houseExpenseRuleRefRepository.save(ref);
                    }

                    /**
                     * 非公租房租金/押金待办事项
                     */
                    // 审核状态 1通过 2不通过
                    if (vo.getAuditStatus() == 1) {
                        propertyRentItem(rentBill.getHouseId(), rentBill.getId(), rentBill.getChargeType(), rentBill.getCost(), tokenRedisVo.getEnterpriseUserId(), rentBill);
                        Map<String, Object> map = new HashMap<>();
                        map.put("billId", rentBill.getId());
                        map.put("billStatus", rentBill.getChargeType());
                        map.put("billType", 2);
                        map.put("billTime", rentBill.getCreateTime());
                        bills.add(map);

                    } else {
                        // 账单审核不通过之后停止自动生成，并清除账单规则关联表
                        if (ref != null) {
                            houseExpenseRuleRefRepository.delete(ref);
                        }
                    }
                }
                estateRentBillRepository.saveAll(rentBills);
            }

            // 房产其他费用账单ids
            if (vo.getEstateExpenseIds() != null && !vo.getEstateExpenseIds().isEmpty()) {
                List<EstateExpenseBill> expenseBills = estateExpenseBillRepository.findByIdIn(vo.getEstateExpenseIds());
                for (EstateExpenseBill expenseBill : expenseBills) {
                    EstateExpenseBillDetail billDetail = estateExpenseBillDetailRepository.findByExpenseBillId(expenseBill.getId());
                    if (billDetail == null) {
                        return ResultBean.failedResultWithMsg("账单数据有误");
                    }

                    HouseExpenseRuleRef ref = houseExpenseRuleRefRepository.findByRuleIdAndHouseId(expenseBill.getRuleId(), expenseBill.getHouseId());
                    if (ref != null) {
                        ref.setAuditStatus(vo.getAuditStatus());
                        houseExpenseRuleRefRepository.save(ref);
                    }

                    Integer chargeType = expenseBill.getChargeType();
                    if (vo.getAuditStatus() == 1) {
                        switch (chargeType) {
                            case 1:
                                if (billDetail.getWaterStartNum() == null || billDetail.getWaterEndNum() == null) {
                                    return ResultBean.failedResultWithMsg("有账单需完善信息，无法审核");
                                }
                                if (billDetail.getWaterUsage() == null) {
                                    return ResultBean.failedResultWithMsg("未填写用量，无法审核");
                                }
                                break;
                            case 2:
                                if (billDetail.getElectricStartNum() == null || billDetail.getElectricEndNum() == null) {
                                    return ResultBean.failedResultWithMsg("有账单需完善信息，无法审核");
                                }
                                if (billDetail.getElectricUsage() == null) {
                                    return ResultBean.failedResultWithMsg("未填写用量，无法审核");
                                }
                                break;
                            case 6:
                                if (billDetail.getGasStartNum() == null || billDetail.getGasEndNum() == null) {
                                    return ResultBean.failedResultWithMsg("有账单需完善信息，无法审核");
                                }
                                if (billDetail.getGasUsage() == null) {
                                    return ResultBean.failedResultWithMsg("未填写用量，无法审核");
                                }
                                break;
                        }

                        if (expenseBill.getCost() == null || expenseBill.getSurplusCost() == null) {
                            return ResultBean.failedResultWithMsg("有账单需完善信息，无法审核");
                        }
                        /**
                         * 非公租房其他费用账单待办事项
                         */
                        propertyOtherItem(expenseBill.getHouseId(), expenseBill.getId(), expenseBill.getChargeType(), expenseBill.getCost(), tokenRedisVo.getEnterpriseUserId(), expenseBill);
                        Map<String, Object> map = new HashMap<>();
                        map.put("billId", expenseBill.getId());
                        map.put("billStatus", expenseBill.getChargeType());
                        map.put("billType", 1);
                        map.put("billTime", expenseBill.getLastUpdateTime());
                        bills.add(map);
                    } else {
                        // 账单审核不通过之后停止自动生成，并清除账单规则关联表
                        if (ref != null) {
                            houseExpenseRuleRefRepository.delete(ref);
                        }

                    }
                    expenseBill.setAuditStatus(vo.getAuditStatus());
                    expenseBill.setOperatorId(tokenRedisVo.getId());
                    expenseBill.setLastUpdateTime(date);
                }
                estateExpenseBillRepository.saveAll(expenseBills);
                this.remainingDeduction(bills);
            }

        } catch (Exception e) {
            LOG.error(e.getMessage(), e);
            result.setCode(StatusCode.ERROR_CODE_10001.getErrorCode());
            result.setMsg(e.getMessage());
        }
        return result;
    }

    private void remainingDeduction(List<Map<String, Object>> bills) {
        Date updateTime = new Date();
        for (Map<String, Object> bill : bills) {
            Integer operateMoney = 0;

            Integer type = Integer.parseInt(bill.get("billType").toString());
            // 1其他费用
            if (type.equals(1)) {
                Long billId = Long.parseLong(bill.get("billId").toString());
                Optional<EstateExpenseBill> expenseBillById = estateExpenseBillRepository.findById(billId);
                if (expenseBillById.isPresent()) {
                    EstateExpenseBill expenseBill = expenseBillById.get();

                    HouseRemainingRelation relation = houseRemainingRelationRepository.findByOrgIdAndHouseId(expenseBill.getOrgId(), expenseBill.getHouseId());
                    if (relation == null) {
                        continue;
                    }
                    Long id = relation.getRemainingId();
                    Optional<HouseRemaining> byId = houseRemainingRepository.findById(id);
                    HouseRemaining houseRemaining = byId.get();
                    Integer balanceFee = houseRemaining.getBalanceFee();
                    if (balanceFee <= 0) {
                        continue;
                    }


                    Integer cost = expenseBill.getSurplusCost();
                    BigDecimal billCost = new BigDecimal(cost);
                    BigDecimal remaining = new BigDecimal(balanceFee);
                    if (balanceFee - cost <= 0) {// 部分缴费
                        operateMoney = balanceFee;

                        int paymentStatus = DeductionEnum.PART_PAY.getValue();
                        if (balanceFee - cost == 0) {
                            paymentStatus = DeductionEnum.ALL_PAY.getValue();
                        }
                        // 账单费用抵扣
                        cost = Integer.parseInt(billCost.subtract(remaining).toString());
                        expenseBill.setSurplusCost(cost);
                        expenseBill.setPaymentStatus(paymentStatus);
                        expenseBill.setPaymentTime(updateTime);
                        expenseBill.setPaymentChannel(PaymentTypeEnum.Remaining.getValue());
                        EstateExpenseBill saveBill = estateExpenseBillRepository.save(expenseBill);

                        // 余额扣减
                        houseRemaining.setBalanceFee(0);
                        houseRemaining.setLastUpdateTime(updateTime);
                        HouseRemaining saveRemaining = houseRemainingRepository.save(houseRemaining);

                        // 余额操作记录
                        HouseRemainingRecord record = new HouseRemainingRecord();
                        record.setBillId(saveBill.getId());
                        record.setRemainingId(saveRemaining.getId());
                        record.setOperateMoney(operateMoney);
                        record.setOperateName(SystemEnum.SYSTEM_USER_NAME.getValue());
                        record.setLastUpdateTime(updateTime);
                        record.setUpdateStatus(PaymentTypeEnum.SUCCESS.getValue());
                        record.setCause(PaymentCauseEnum.SYSTEM_DEDUCTION.getValue());
                        record.setOperateStatus(PaymentTypeEnum.BUCKLE.getValue());
                        record.setBillStatus(PaymentTypeEnum.EXPENSE_BILL.getValue());
                        // 状态：1正常 2已删除
                        record.setStatus(1);
                        houseRemainingRecordRepository.save(record);

                        // 余额变动待办事项
                        wxPayService.houseRemainingItem(saveBill.getHouseId(), saveBill.getMatchUserId(), saveRemaining.getId(), expenseBill.getChargeType(), operateMoney, null, saveBill.getOrgId());
                    } else {
                        operateMoney = cost;
                        // 账单费用扣减
                        cost = Integer.parseInt(remaining.subtract(billCost).toString());
                        expenseBill.setSurplusCost(0);
                        expenseBill.setPaymentTime(updateTime);
                        expenseBill.setPaymentChannel(PaymentTypeEnum.Remaining.getValue());
                        expenseBill.setPaymentStatus(RrentBillPaymentStatusTypeEnum.PAY.getValue());
                        EstateExpenseBill saveBill = estateExpenseBillRepository.save(expenseBill);

                        // 余额扣减
                        houseRemaining.setBalanceFee(cost);
                        houseRemaining.setLastUpdateTime(updateTime);
                        HouseRemaining saveRemaining = houseRemainingRepository.save(houseRemaining);

                        // 余额操作记录
                        HouseRemainingRecord record = new HouseRemainingRecord();
                        record.setBillId(saveBill.getId());
                        record.setRemainingId(saveRemaining.getId());
                        record.setOperateMoney(operateMoney);
                        record.setOperateName(SystemEnum.SYSTEM_USER_NAME.getValue());
                        record.setLastUpdateTime(updateTime);
                        record.setUpdateStatus(PaymentTypeEnum.SUCCESS.getValue());
                        record.setCause(PaymentCauseEnum.SYSTEM_DEDUCTION.getValue());
                        record.setOperateStatus(PaymentTypeEnum.BUCKLE.getValue());
                        record.setBillStatus(PaymentTypeEnum.EXPENSE_BILL.getValue());
                        // 状态：1正常 2已删除
                        record.setStatus(1);
                        houseRemainingRecordRepository.save(record);

                        // 余额变动待办事项
                        wxPayService.houseRemainingItem(saveBill.getHouseId(), saveBill.getMatchUserId(), saveRemaining.getId(), expenseBill.getChargeType(), operateMoney, null, saveBill.getOrgId());
                        // 其他费用待办事项
                        wxPayService.propertyOtherItem(saveBill.getId());
                    }
                }
            } else {
                Long billId = Long.parseLong(bill.get("billId").toString());
                Optional<EstateRentBill> rentBillById = estateRentBillRepository.findById(billId);
                if (rentBillById.isPresent()) {
                    EstateRentBill estateRentBill = rentBillById.get();

                    HouseRemainingRelation relation = houseRemainingRelationRepository.findByOrgIdAndHouseId(estateRentBill.getOrgId(), estateRentBill.getHouseId());
                    if (relation == null) {
                        continue;
                    }
                    Long id = relation.getRemainingId();
                    Optional<HouseRemaining> byId = houseRemainingRepository.findById(id);
                    HouseRemaining houseRemaining = byId.get();
                    Integer balanceFee = houseRemaining.getBalanceFee();
                    if (balanceFee <= 0) {
                        continue;
                    }


                    Integer rentBillChargeType = estateRentBill.getChargeType();
                    Integer cost = estateRentBill.getSurplusCost();
                    BigDecimal billCost = new BigDecimal(cost);
                    BigDecimal remaining = new BigDecimal(balanceFee);
                    if (rentBillChargeType == 1) {// 租金
                        if (balanceFee - cost <= 0) {
                            operateMoney = balanceFee;

                            int paymentStatus = DeductionEnum.PART_PAY.getValue();
                            if (balanceFee - cost == 0) {
                                paymentStatus = DeductionEnum.ALL_PAY.getValue();
                            }
                            // 账单费用抵扣
                            cost = Integer.parseInt(billCost.subtract(remaining).toString());
                            estateRentBill.setSurplusCost(cost);
                            estateRentBill.setPaymentStatus(paymentStatus);
                            estateRentBill.setPaymentTime(updateTime);
                            EstateRentBill saveBill = estateRentBillRepository.save(estateRentBill);

                            // 余额扣减
                            houseRemaining.setBalanceFee(0);
                            houseRemaining.setLastUpdateTime(updateTime);
                            HouseRemaining saveRemaining = houseRemainingRepository.save(houseRemaining);

                            // 余额操作记录
                            HouseRemainingRecord record = new HouseRemainingRecord();
                            record.setBillId(saveBill.getId());
                            record.setRemainingId(saveRemaining.getId());
                            record.setOperateMoney(operateMoney);
                            record.setOperateName(SystemEnum.SYSTEM_USER_NAME.getValue());
                            record.setLastUpdateTime(updateTime);
                            record.setUpdateStatus(PaymentTypeEnum.SUCCESS.getValue());
                            record.setCause(PaymentCauseEnum.SYSTEM_DEDUCTION.getValue());
                            record.setOperateStatus(PaymentTypeEnum.BUCKLE.getValue());
                            record.setBillStatus(PaymentTypeEnum.RENT_BILL.getValue());
                            // 状态：1正常 2已删除
                            record.setStatus(1);
                            houseRemainingRecordRepository.save(record);

                            // 余额变动待办事项
                            wxPayService.houseRemainingItem(saveBill.getHouseId(), saveBill.getMatchUserId(), saveRemaining.getId(), estateRentBill.getChargeType() == 1 ? 8 : 9, operateMoney, null, saveBill.getOrgId());
                        } else {
                            operateMoney = cost;
                            // 账单费用扣减
                            cost = Integer.parseInt(remaining.subtract(billCost).toString());
                            estateRentBill.setSurplusCost(0);
                            estateRentBill.setPaymentTime(updateTime);
                            estateRentBill.setPaymentStatus(RrentBillPaymentStatusTypeEnum.PAY.getValue());
                            EstateRentBill saveBill = estateRentBillRepository.save(estateRentBill);

                            // 余额扣减
                            houseRemaining.setBalanceFee(cost);
                            houseRemaining.setLastUpdateTime(updateTime);
                            HouseRemaining saveRemaining = houseRemainingRepository.save(houseRemaining);

                            // 余额操作记录
                            HouseRemainingRecord record = new HouseRemainingRecord();
                            record.setBillId(saveBill.getId());
                            record.setRemainingId(saveRemaining.getId());
                            record.setOperateMoney(operateMoney);
                            record.setOperateName(SystemEnum.SYSTEM_USER_NAME.getValue());
                            record.setLastUpdateTime(updateTime);
                            record.setUpdateStatus(PaymentTypeEnum.SUCCESS.getValue());
                            record.setCause(PaymentCauseEnum.SYSTEM_DEDUCTION.getValue());
                            record.setOperateStatus(PaymentTypeEnum.BUCKLE.getValue());
                            record.setBillStatus(PaymentTypeEnum.RENT_BILL.getValue());
                            // 状态：1正常 2已删除
                            record.setStatus(1);
                            houseRemainingRecordRepository.save(record);

                            // 余额变动待办事项
                            wxPayService.houseRemainingItem(saveBill.getHouseId(), saveBill.getMatchUserId(), saveRemaining.getId(), estateRentBill.getChargeType() == 1 ? 8 : 9, operateMoney, null, saveBill.getOrgId());
                            // 租金/押金待办事项
                            wxPayService.propertyRentItem(saveBill.getId());
                        }
                    } else {
                        // 押金
                        if (balanceFee - cost <= 0) {
                            operateMoney = balanceFee;

                            int paymentStatus = DeductionEnum.PART_PAY.getValue();
                            if (balanceFee - cost == 0) {
                                paymentStatus = DeductionEnum.ALL_PAY.getValue();
                            }
                            // 账单费用抵扣
                            cost = Integer.parseInt(billCost.subtract(remaining).toString());
                            estateRentBill.setSurplusCost(cost);
                            estateRentBill.setPaymentStatus(paymentStatus);
                            estateRentBill.setPaymentTime(updateTime);
                            EstateRentBill saveBill = estateRentBillRepository.save(estateRentBill);

                            // 余额扣减
                            houseRemaining.setBalanceFee(0);
                            houseRemaining.setCashPledge(operateMoney);
                            houseRemaining.setLastUpdateTime(updateTime);
                            HouseRemaining saveRemaining = houseRemainingRepository.save(houseRemaining);

                            // 余额操作记录
                            HouseRemainingRecord record = new HouseRemainingRecord();
                            record.setBillId(saveBill.getId());
                            record.setRemainingId(saveRemaining.getId());
                            record.setOperateMoney(operateMoney);
                            record.setOperateName(SystemEnum.SYSTEM_USER_NAME.getValue());
                            record.setLastUpdateTime(updateTime);
                            record.setUpdateStatus(PaymentTypeEnum.SUCCESS.getValue());
                            record.setCause(PaymentCauseEnum.SYSTEM_DEDUCTION.getValue());
                            record.setOperateStatus(PaymentTypeEnum.BUCKLE.getValue());
                            record.setBillStatus(PaymentTypeEnum.DEPOSIT_BILL.getValue());
                            // 状态：1正常 2已删除
                            record.setStatus(1);
                            houseRemainingRecordRepository.save(record);

                            // 余额变动待办事项
                            wxPayService.houseRemainingItem(saveBill.getHouseId(), saveBill.getMatchUserId(), saveRemaining.getId(), estateRentBill.getChargeType() == 1 ? 8 : 9, operateMoney, null, saveBill.getOrgId());
                        } else {
                            operateMoney = cost;
                            // 账单费用扣减
                            cost = Integer.parseInt(remaining.subtract(billCost).toString());
                            estateRentBill.setSurplusCost(0);
                            estateRentBill.setPaymentTime(updateTime);
                            estateRentBill.setPaymentStatus(RrentBillPaymentStatusTypeEnum.PAY.getValue());
                            EstateRentBill saveBill = estateRentBillRepository.save(estateRentBill);

                            // 余额扣减
                            houseRemaining.setBalanceFee(cost);
                            houseRemaining.setCashPledge(operateMoney);
                            houseRemaining.setLastUpdateTime(updateTime);
                            HouseRemaining saveRemaining = houseRemainingRepository.save(houseRemaining);

                            // 余额操作记录
                            HouseRemainingRecord record = new HouseRemainingRecord();
                            record.setBillId(saveBill.getId());
                            record.setRemainingId(saveRemaining.getId());
                            record.setOperateMoney(operateMoney);
                            record.setOperateName(SystemEnum.SYSTEM_USER_NAME.getValue());
                            record.setLastUpdateTime(updateTime);
                            record.setUpdateStatus(PaymentTypeEnum.SUCCESS.getValue());
                            record.setCause(PaymentCauseEnum.SYSTEM_DEDUCTION.getValue());
                            record.setOperateStatus(PaymentTypeEnum.BUCKLE.getValue());
                            record.setBillStatus(PaymentTypeEnum.DEPOSIT_BILL.getValue());
                            // 状态：1正常 2已删除
                            record.setStatus(1);
                            houseRemainingRecordRepository.save(record);

                            // 余额变动待办事项
                            wxPayService.houseRemainingItem(saveBill.getHouseId(), saveBill.getMatchUserId(), saveRemaining.getId(), estateRentBill.getChargeType() == 1 ? 8 : 9, operateMoney, null, saveBill.getOrgId());
                            // 租金/押金待办事项
                            wxPayService.propertyRentItem(saveBill.getId());
                        }
                    }
                }
            }

        }


    }

    /**
     * 账单列表-设置
     *
     * @param accessToken
     * @param vo
     * @return 新增支付记录 如是非公租房 是退租账单则更新退租管理状态
     */
    public ResultBean<Object> setupBill(String accessToken, EditBillRequestVO vo) {
        try {
            TokenRedisVo tokenRedisVo = redisService.findTokenVo(accessToken);
            if (tokenRedisVo == null) {
                return ResultBean.failedResultOfToken();
            }

            if (vo.getId() == null || vo.getChargeType() == null || vo.getPaymentChannel() == null) {
                return ResultBean.failedResultOfParamWithMsg("参数错误");
            }

            if (vo.getHouseType() == null) {
                return ResultBean.failedResultOfParamWithMsg("房屋类型为空");
            }

            Date date = new Date();

            if (vo.getHouseType() == 1) { // 公租房
                Integer cost = null;
                String matchUserId = null;
                if (vo.getChargeType() == 8 || vo.getChargeType() == 9) { // 押金、租金
                    Optional<RentBill> rentBillOptional = rentBillRepository.findById(vo.getId());
                    if (!rentBillOptional.isPresent()) {
                        return ResultBean.failedResultWithMsg("账单信息不存在");
                    }
                    RentBill rentBill = rentBillOptional.get();
                    rentBill.setPaymentStatus(1);
                    rentBill.setPaymentTime(date);
                    rentBill.setPaymentChannel(vo.getPaymentChannel());
                    rentBillRepository.save(rentBill);
                    cost = rentBill.getCost();
                    matchUserId = rentBill.getMatchUserId();
                } else { // 其他费用
                    Optional<ExpenseBill> expenseBillOptional = expenseBillRepository.findById(vo.getId());
                    if (!expenseBillOptional.isPresent()) {
                        return ResultBean.failedResultWithMsg("账单信息不存在");
                    }
                    ExpenseBill expenseBill = expenseBillOptional.get();
                    expenseBill.setPaymentStatus(1);
                    expenseBill.setPaymentTime(date);
                    expenseBill.setPaymentChannel(vo.getPaymentChannel());
                    expenseBill.setOperatorId(tokenRedisVo.getId());
                    expenseBill.setLastUpdateTime(date);
                    expenseBillRepository.save(expenseBill);
                    cost = expenseBill.getCost();
                    matchUserId = expenseBill.getMatchUserId();
                }

                // 生成支付记录
                String orderNumber = String.valueOf(KeyWorker.nextId());
                HousePaymentOrderRelation por = new HousePaymentOrderRelation();
                por.setBillId(vo.getId());
                por.setOrderNumber(orderNumber);
                por.setPlatformCommissionAmount(0);
                por.setBillAmount(cost);
                // 对应账单支付类型
                if (vo.getChargeType() == 1) {
                    por.setBusinessType(BusinessTypeEnum.WATER_FEE.getValue());
                } else if (vo.getChargeType() == 2) {
                    por.setBusinessType(BusinessTypeEnum.ELECTRIC_FEE.getValue());
                } else if (vo.getChargeType() == 3) {
                    por.setBusinessType(BusinessTypeEnum.PROPERTY_FEE.getValue());
                } else if (vo.getChargeType() == 4) {
                    por.setBusinessType(BusinessTypeEnum.BROADBAND_FEE.getValue());
                } else if (vo.getChargeType() == 5) {
                    por.setBusinessType(BusinessTypeEnum.TV_FEE.getValue());
                } else if (vo.getChargeType() == 6) {
                    por.setBusinessType(BusinessTypeEnum.COALGAS_FEE.getValue());
                } else if (vo.getChargeType() == 7) {
                    por.setBusinessType(BusinessTypeEnum.HEATING_FEE.getValue());
                } else if (vo.getChargeType() == 8) {
                    por.setBusinessType(BusinessTypeEnum.RENT_FEE.getValue());
                } else if (vo.getChargeType() == 9) {
                    por.setBusinessType(BusinessTypeEnum.DEPOSIT_FEE.getValue());
                } else if (vo.getChargeType() == 10) {
                    por.setBusinessType(BusinessTypeEnum.OTHER_FEE.getValue());
                }
                housePaymentOrderRelationRepository.save(por);

                HousePaymentOrder paymentOrder = new HousePaymentOrder();
                paymentOrder.setOrderNumber(orderNumber);
                paymentOrder.setBillAmount(cost);
                paymentOrder.setPlatformCommissionAmount(0);
                paymentOrder.setWxCommissionAmount(0);
                paymentOrder.setTotalAmount(cost);
                paymentOrder.setPayUserId(matchUserId);
                paymentOrder.setPayResult(1);
                paymentOrder.setPayTime(date);
                housePaymentOrderRepository.save(paymentOrder);
            }

            // 非公租房
            if (vo.getHouseType() == 2) {
                Integer cost = null;
                String matchUserId = null;
                if (vo.getChargeType() == 8 || vo.getChargeType() == 9) {
                    Optional<EstateRentBill> estateRentBillOptional = estateRentBillRepository.findById(vo.getId());
                    if (!estateRentBillOptional.isPresent()) {
                        return ResultBean.failedResultWithMsg("账单信息不存在");
                    }
                    EstateRentBill rentBill = estateRentBillOptional.get();
                    rentBill.setPaymentStatus(1);
                    rentBill.setPaymentTime(date);
                    rentBill.setPaymentChannel(vo.getPaymentChannel());
                    estateRentBillRepository.save(rentBill);
                    cost = rentBill.getCost();
                    matchUserId = rentBill.getMatchUserId();
                } else {
                    Optional<EstateExpenseBill> estateExpenseBillOptional = estateExpenseBillRepository.findById(vo.getId());
                    if (!estateExpenseBillOptional.isPresent()) {
                        return ResultBean.failedResultWithMsg("账单信息不存在");
                    }
                    EstateExpenseBill expenseBill = estateExpenseBillOptional.get();
                    expenseBill.setPaymentStatus(1);
                    expenseBill.setPaymentTime(date);
                    expenseBill.setPaymentChannel(vo.getPaymentChannel());
                    expenseBill.setOperatorId(tokenRedisVo.getId());
                    expenseBill.setLastUpdateTime(date);
                    estateExpenseBillRepository.save(expenseBill);
                    cost = expenseBill.getCost();
                    matchUserId = expenseBill.getMatchUserId();

                    // 如果是退租账单，更新退租管理状态
                    if (expenseBill.getChargeType() != null && expenseBill.getChargeType() == 10) {
                        // 更新退租账单状态
                        withDrawService.changeStatus(expenseBill.getId(), expenseBill.getHouseId());
                    }
                }

                // 生成支付记录
                String orderNumber = String.valueOf(KeyWorker.nextId());
                EstatePaymentOrderRelation por = new EstatePaymentOrderRelation();
                por.setBillId(vo.getId());
                por.setOrderNumber(orderNumber);
                por.setPlatformCommissionAmount(0);
                por.setBillAmount(cost);
                if (vo.getChargeType() == 1) {
                    por.setBusinessType(BusinessTypeEnum.WATER_FEE.getValue());
                } else if (vo.getChargeType() == 2) {
                    por.setBusinessType(BusinessTypeEnum.ELECTRIC_FEE.getValue());
                } else if (vo.getChargeType() == 3) {
                    por.setBusinessType(BusinessTypeEnum.PROPERTY_FEE.getValue());
                } else if (vo.getChargeType() == 4) {
                    por.setBusinessType(BusinessTypeEnum.BROADBAND_FEE.getValue());
                } else if (vo.getChargeType() == 5) {
                    por.setBusinessType(BusinessTypeEnum.TV_FEE.getValue());
                } else if (vo.getChargeType() == 6) {
                    por.setBusinessType(BusinessTypeEnum.COALGAS_FEE.getValue());
                } else if (vo.getChargeType() == 7) {
                    por.setBusinessType(BusinessTypeEnum.HEATING_FEE.getValue());
                } else if (vo.getChargeType() == 8) {
                    por.setBusinessType(BusinessTypeEnum.RENT_FEE.getValue());
                } else if (vo.getChargeType() == 9) {
                    por.setBusinessType(BusinessTypeEnum.DEPOSIT_FEE.getValue());
                } else if (vo.getChargeType() == 10) {
                    por.setBusinessType(BusinessTypeEnum.OTHER_FEE.getValue());
                }
                estatePaymentOrderRelationRepository.save(por);

                EstatePaymentOrder paymentOrder = new EstatePaymentOrder();
                paymentOrder.setOrderNumber(orderNumber);
                paymentOrder.setBillAmount(cost);
                paymentOrder.setPlatformCommissionAmount(0);
                paymentOrder.setWxCommissionAmount(0);
                paymentOrder.setTotalAmount(cost);
                paymentOrder.setPayUserId(matchUserId);
                paymentOrder.setPayResult(1);
                paymentOrder.setPayTime(date);
                estatePaymentOrderRepository.save(paymentOrder);
            }
            return ResultBean.successfulResult(null);
        } catch (Exception e) {
            LOG.error(e.getMessage(), e);
            return ResultBean.failedResultOfException();
        }
    }

    /*
     *租金列表-列表信息展示
     */
    public ResultBean<Object> rentList(String accessToken, Integer pageNumber, Integer pageSize, Integer type, BillSearchRequestVO filter) {
        LOG.info("estateBillService rentList accessToken={},pageNumber={},pageSize={},type={}", accessToken, pageNumber, pageSize, type);
        ResultBean<Object> result = new ResultBean<>();
        result.setMsg(StatusCode.SUCCESS_CODE_10000.getErrorMsg());
        result.setCode(StatusCode.SUCCESS_CODE_10000.getErrorCode());
        TokenRedisVo tokenRedisVo = redisService.findTokenVo(accessToken);
        ApiData<Map<String, Object>> apiData = new ApiData<>();
        try {
            if (tokenRedisVo == null) {
                result.setCode(StatusCode.ERROR_CODE_40001.getErrorCode());
                result.setMsg(StatusCode.ERROR_CODE_40001.getErrorMsg());
                return result;
            }

            String billSql = "SELECT  b.`id`,b.`house_id` AS houseId,b.`community_id` AS communityId,a1.name AS name1,a2.name AS name2,b.`building_num` AS buildingNum,"
                    + "b.`unit`,b.`room_number` AS roomNumber,b.`org_id` AS orgId,b.`match_user_id` AS matchUserId,b.`charge_type` AS chargeType,"
                    + "b.`measure_unit` AS measureUnit,b.`cost`, b.`start_time`  AS startTime, b.`end_time` AS endTime,b.`payment_status`  AS paymentStatus,"
                    + "b.`payment_time` AS paymentTime,b.`create_time` AS createTime "
                    + "FROM `saas_estate_rent_bill` b "
                    + "LEFT JOIN  `saas_app_user` a1  ON a1.`id` = b.match_user_id "
                    + "LEFT JOIN  `saas_app_user` a2  ON a2.`enterprise_user_id` = b.match_user_id "
                    + "WHERE 1=1 \n";

            if (type != 0) {
                switch (type) {
                    case 1:
                        billSql += "  AND b.payment_status=0";
                        break;
                    case 2:
                        billSql += "  AND b.payment_status=1";
                        break;
                }
            }

            String offsets = "   LIMIT ? OFFSET ? ";
            List<Object> args = new ArrayList<>();
            StringBuilder where = new StringBuilder();
            if (StringUtils.isNotBlank(filter.getHouseId())) {
                where.append("  AND  b.`house_id` = ?  \n");
                args.add(filter.getHouseId());
            }
            if (StringUtils.isNotBlank(filter.getCommunityId())) {
                where.append("  AND  b.`community_id` = ?  \n");
                args.add(filter.getCommunityId());
            }
            if (filter.getBuildingNum() != null) {
                where.append("  AND  b.`building_num` =  ?  \n");
                args.add(filter.getBuildingNum());
            }
            if (filter.getUnit() != null) {
                where.append("  AND  b.`unit` =  ?  \n");
                args.add(filter.getUnit());
            }
            if (StringUtils.isNotBlank(filter.getRoomNumber())) {
                where.append("  AND  b.`room_number` = ?  \n");
                args.add(filter.getRoomNumber());
            }
            if (filter.getOrgId() != null) {
                where.append("  AND  b.`org_id` =  ?  \n");
                args.add(filter.getOrgId());
            }
            if (StringUtils.isNotBlank(filter.getMatchUserName())) {
                where.append("  AND  (a1.`name` = ? or a2.`name` = ?) \n");
                args.add("%" + filter.getMatchUserName() + "%");
                args.add("%" + filter.getMatchUserName() + "%");
            }
            if (filter.getChargeType() != null) {
                where.append("  AND  b.`charge_type` =  ?  \n");
                args.add(filter.getChargeType());
            }
            if (filter.getPaymentStatus() != null) {
                where.append("  AND  b.`payment_status` =  ?  \n");
                args.add(filter.getPaymentStatus());
            }
            // 查询总记录数
            String count = "select count(1) FROM (" + billSql + where + ") c  ";
            Long total = jdbcTemplate.queryForObject(count, Long.class, args.toArray());
            LOG.info("SQL :" + billSql + where);
            if (total == 0) {
                result.setCode(StatusCode.SUCCESS_CODE_10000.getErrorCode());
                result.setMsg(StatusCode.SUCCESS_CODE_10000.getErrorMsg());
                result.setResultData(null);
                return result;
            }
            // 查询分页数据
            where.append("  ORDER BY ").append(" b.create_time,b.payment_time desc ").append(" \n").append(offsets);
            args.add(pageSize);
            args.add(pageNumber * pageSize);
            List<Map<String, Object>> peopleList = jdbcTemplate.queryForList(billSql + where, args.toArray());
            for (Map<String, Object> map : peopleList) {
                String communityId = String.valueOf(map.get("communityId"));
                Long buildingNum = Long.valueOf(String.valueOf(map.get("buildingNum")));
                Long unit = Long.valueOf(String.valueOf(map.get("unit")));
                Long orgId = Long.valueOf(String.valueOf(map.get("orgId")));
                String matchUserId = String.valueOf(map.get("matchUserId"));
                String cost = fenToYuan(String.valueOf(map.get("cost")));
                map.put("cost", cost);
                Optional<Community> communityOptional = communityRepository.findById(communityId);
                // 获取小区名称
                if (communityOptional.isPresent()) {
                    Community community = communityOptional.get();
                    map.put("communityName", community.getName());
                }
                // 获取楼栋名称
                Optional<CommunityBuild> communityBuildOptional = communityBuildRepository.findById(buildingNum);
                if (communityBuildOptional.isPresent()) {
                    CommunityBuild communityBuild = communityBuildOptional.get();
                    map.put("buildingNumName", communityBuild.getName());
                }
                // 获取单元名称
                Optional<CommunityBuildUnit> communityBuildUnitOptional = communityBuildUnitRepository.findById(unit);
                if (communityBuildUnitOptional.isPresent()) {
                    CommunityBuildUnit communityBuildUnit = communityBuildUnitOptional.get();
                    map.put("unitName", communityBuildUnit.getName());
                }
                // 获取产权单位名称
                DepartmentHierarchy propertyUnit = departmentHierarchyRepository.findByOrgId(orgId);
                if (propertyUnit != null) {
                    map.put("orgName", propertyUnit.getName());
                }
                // 获取承租人名称、手机号
                AppUser lesseeUser = appUserRepository.findByIdOrEnterpriseUserId(matchUserId, matchUserId);
                if (lesseeUser != null) {
                    map.put("matchUserName", lesseeUser.getName());
                    map.put("matchUserPhone", lesseeUser.getPhone());
                    map.put("name", lesseeUser.getName());
                }
            }
            apiData.setData(peopleList);
            apiData.setTotal(total);
            LOG.info("SQL :" + billSql + where);
            result.setResultData(apiData);
        } catch (Exception e) {
            LOG.error(e.getMessage(), e);
            result.setCode(StatusCode.ERROR_CODE_10001.getErrorCode());
            result.setMsg(e.getMessage());
        }
        return result;
    }

    /**
     * 账单生成-列表展示
     *
     * @param accessToken
     * @param pageNumber
     * @param pageSize
     * @param id
     * @param community   查当前名下管理的所有账单信息
     */
    public ResultBean<Object> createBillList(String accessToken, Integer pageNumber, Integer pageSize, String id, String community, Integer orgId, String name) {
        LOG.info("estateBillService createBillList accessToken={},pageNumber={},pageSize={},id={},community={}", accessToken, pageNumber, pageSize, id, community);
        ResultBean<Object> result = new ResultBean<>();
        result.setMsg(StatusCode.SUCCESS_CODE_10000.getErrorMsg());
        result.setCode(StatusCode.SUCCESS_CODE_10000.getErrorCode());
        TokenRedisVo tokenRedisVo = redisService.findTokenVo(accessToken);
        ApiData<Map<String, Object>> apiData = new ApiData<>();
        try {
            // 校验登录信息
            if (tokenRedisVo == null) {
                result.setCode(StatusCode.ERROR_CODE_40001.getErrorCode());
                result.setMsg(StatusCode.ERROR_CODE_40001.getErrorMsg());
                return result;
            }

            String billSql = "SELECT  r.`id`,r.`community_id` AS communityId,r.name,r.`number`,r.`start_time` AS startTime,\n" +
                    " r.`end_time` AS endTime,r.`close_time` AS closeTime,r.`charge_type` AS chargeType,r.`operator_id` AS operatorId,\n" +
                    "r.`create_time` AS createTime ,r.create_status as createStatus FROM `saas_house_expense_bill_rule` r left join saas_house_expense_rule_ref rf on r.id=rf.rule_id WHERE 1=1 and r.type = 2  \n";
            List<Object> args = new ArrayList<>();
            StringBuilder where = new StringBuilder();

            // 权限过滤
            List<Map<String, Object>> communityList = appCommunityService.findCommunity(accessToken).getResultData();
            if (communityList != null && !communityList.isEmpty()) {
                where.append(" and rf.community_id in " + SqlUtils.foreachIn(communityList.size()));
                List<String> communityIds = communityList.stream().map(map -> {
                    String comId = map.get("communityId").toString();
                    return comId;
                }).collect(Collectors.toList());
                args.addAll(communityIds);
            } else {
                where.append(" and rf.community_id in (null)");
            }

            String offsets = "   LIMIT ? OFFSET ? ";

            if (StringUtils.isNotBlank(id)) {
                where.append("  AND  r.`id` = ?  \n");
                args.add(id);
            }
            if (StringUtils.isNotBlank(community)) {
                where.append("  AND  r.`community_id` = ?  \n");
                args.add(community);
            }
            if (null != orgId) {
                where.append("  AND  r.`collection_org` = ?  \n");
                args.add(orgId);
            }

            if (StringUtils.isNotBlank(name)) {
                where.append("  AND  r.`name` like ?   \n");
                args.add("%" + name + "%");
            }
            String group = " group by r.id  ";

            // 查询总记录数
            String count = "select count(1) FROM (" + billSql + where + group + ")  c  ";
            Long total = jdbcTemplate.queryForObject(count, Long.class, args.toArray());
            LOG.info("SQL :" + billSql + where);
            if (total == 0) {
                result.setCode(StatusCode.SUCCESS_CODE_10000.getErrorCode());
                result.setMsg(StatusCode.SUCCESS_CODE_10000.getErrorMsg());
                result.setResultData(null);
                return result;
            }

            where.append(group);
            // 查询分页数据
            where.append("  ORDER BY ").append("  r.create_time desc ").append(" \n").append(offsets);
            args.add(pageSize);
            args.add(pageNumber * pageSize);

            List<Map<String, Object>> peopleList = jdbcTemplate.queryForList(billSql + where, args.toArray());
            for (Map<String, Object> map : peopleList) {
                String communityId = String.valueOf(map.get("communityId"));
                String operatorId = String.valueOf(map.get("operatorId"));

                // 获取小区名称
                Optional<Community> communityOptional = communityRepository.findById(communityId);
                if (communityOptional.isPresent()) {
                    Community com = communityOptional.get();
                    map.put("communityName", com.getName());
                }
                // 获取创建人名称
                User operatorUser = userRepository.findById(operatorId);
                if (operatorUser != null) {
                    map.put("operatorUserName", operatorUser.getName());
                }
            }
            apiData.setData(peopleList);
            apiData.setTotal(total);
            LOG.info("SQL :" + billSql + where);
            result.setResultData(apiData);
        } catch (Exception e) {
            LOG.error(e.getMessage(), e);
            result.setCode(StatusCode.ERROR_CODE_10001.getErrorCode());
            result.setMsg(e.getMessage());
        }
        return result;
    }

    /*
     *账单生成-生成账单
     */
    public ResultBean<Object> createBill(String accessToken, CreateBillRequestParamVO createBillRequestParamVO) {
        LOG.info("estateBillService createBill accessToken={},createBillRequestParamVO={}", accessToken, createBillRequestParamVO);
        ResultBean<Object> result = new ResultBean<>();
        result.setMsg(StatusCode.SUCCESS_CODE_10000.getErrorMsg());
        result.setCode(StatusCode.SUCCESS_CODE_10000.getErrorCode());
        TokenRedisVo tokenRedisVo = redisService.findTokenVo(accessToken);
        try {
            if (tokenRedisVo == null) {
                result.setCode(StatusCode.ERROR_CODE_40001.getErrorCode());
                result.setMsg(StatusCode.ERROR_CODE_40001.getErrorMsg());
                return result;
            }
            HouseExpenseBillRuleVO houseExpenseBillRuleVO = createBillRequestParamVO.getHouseExpenseBillRuleVO();
            List<HouseExpenseRuleRefVO> houseExpenseRuleRefVOS = createBillRequestParamVO.getHouseExpenseRuleRefVOS();

            // 查找是否存在收费项目，不存在则无法生成  [收费项目(1水费 2电费 3物业费 4宽带费 5电视费 6煤气费 7暖气费 8租金 9押金)]
            List<String> failRoomNumberList = new ArrayList<>(); // 不存在收费项目的房号
            if (houseExpenseBillRuleVO.getChargeType() == 8 || houseExpenseBillRuleVO.getChargeType() == 9) {
                Integer chargeType = null; // 收费类型：1房租 2押金
                if (houseExpenseBillRuleVO.getChargeType() == 8) {
                    chargeType = 1;
                } else {
                    chargeType = 2;
                }
                // 收费项目-房租/押金账单
                for (HouseExpenseRuleRefVO vo : houseExpenseRuleRefVOS) {
                    String houseId = vo.getHouseId();
                    List<HouseExpenseRent> list = houseExpenseRentRepository.findByHouseIdAndChargeTypeAndEnableStatusAndStatus(houseId, chargeType, 1, 1);
                    if (list.size() == 0) { // 不存在收费项目
                        failRoomNumberList.add(vo.getRoomNumber());
                    }
                }

            } else {

                // 收费项目-其它费用设置
                for (HouseExpenseRuleRefVO vo : houseExpenseRuleRefVOS) {
                    String houseId = vo.getHouseId();
                    List<HouseExpenseHouse> list = houseExpenseHouseRepository.findByHouseId(houseId);
                    // 项目费用id
                    List<String> expenseIdList = list.stream().map(HouseExpenseHouse::getExpenseId).collect(Collectors.toList());
                    // 绑定房的相关类型费用
                    List<Integer> chargeTypeList = houseExpenseOtherRepository.findByIdInAndEnableStatusAndStatus(expenseIdList, 1, 1).stream().map(HouseExpenseOther::getChargeType).collect(Collectors.toList());

                    if (!chargeTypeList.contains(houseExpenseBillRuleVO.getChargeType())) {  // 生成的类型不包含在收费项目里
                        failRoomNumberList.add(vo.getRoomNumber());
                    }
                }

            }

            // 返回失败房号
            if (failRoomNumberList.size() > 0) {
                return ResultBean.failedResultWithMsg(failRoomNumberList.toString() + "房号不存在收费项目，无法生成账单");
            }

            if (!houseExpenseBillRuleVO.getChargeType().equals(9)) {
                for (HouseExpenseRuleRefVO houseExpenseRuleRefVO : houseExpenseRuleRefVOS) {
                    List<HouseExpenseRuleRef> existHouseExpenseRule = houseExpenseRuleRefRepository.findByHouseId(houseExpenseRuleRefVO.getHouseId());
                    for (HouseExpenseRuleRef houseExpenseRuleRef : existHouseExpenseRule) {
                        HouseExpenseBillRule houseExpenseBillRule = houseExpenseBillRuleRepository.findById(houseExpenseRuleRef.getRuleId());
                        if (houseExpenseBillRule.getChargeType().equals(houseExpenseBillRuleVO.getChargeType())) {
                            if ((houseExpenseBillRuleVO.getStartTime().getTime() < houseExpenseBillRule.getStartTime().getTime() && houseExpenseBillRuleVO.getEndTime().getTime() >= houseExpenseBillRule.getStartTime().getTime())
                                    || (houseExpenseBillRuleVO.getStartTime().getTime() >= houseExpenseBillRule.getStartTime().getTime() && houseExpenseBillRuleVO.getEndTime().getTime() <= houseExpenseBillRule.getEndTime().getTime())
                                    || (houseExpenseBillRuleVO.getStartTime().getTime() <= houseExpenseBillRule.getEndTime().getTime() && houseExpenseBillRuleVO.getEndTime().getTime() > houseExpenseBillRule.getEndTime().getTime())) {
                                return ResultBean.failedResultWithMsg("周期内账单已存在");
                            }
                        }
                    }
                }

            } else {   // 押金

                for (HouseExpenseRuleRefVO houseExpenseRuleRefVO : houseExpenseRuleRefVOS) {
                    List<HouseExpenseRuleRef> existHouseExpenseRule = houseExpenseRuleRefRepository.findByHouseId(houseExpenseRuleRefVO.getHouseId());
                    for (HouseExpenseRuleRef houseExpenseRuleRef : existHouseExpenseRule) {
                        HouseExpenseBillRule houseExpenseBillRule = houseExpenseBillRuleRepository.findById(houseExpenseRuleRef.getRuleId());
                        if (houseExpenseBillRule.getChargeType().equals(houseExpenseBillRuleVO.getChargeType())) {
                            return ResultBean.failedResultWithMsg("押金账单已存在");
                        }
                    }
                }
            }

            houseExpenseBillRuleVO.setId("ZD" + KeyWorker.nextId());
            houseExpenseBillRuleVO.setNumber(houseExpenseRuleRefVOS.size());
            houseExpenseBillRuleVO.setOperatorId(tokenRedisVo.getId());
            // houseExpenseBillRuleVO.setCreateTime(new Date());
            HouseExpenseBillRule houseExpenseBillRule = new HouseExpenseBillRule();
            BeanUtils.copyProperties(houseExpenseBillRuleVO, houseExpenseBillRule);
            HouseExpenseBillRule save = houseExpenseBillRuleRepository.save(houseExpenseBillRule);
            if (save != null) {
                houseExpenseRuleRefVOS.forEach(h -> {
                    HouseExpenseRuleRef houseExpenseRuleRef = new HouseExpenseRuleRef();
                    h.setCommunityId(save.getCommunityId());
                    h.setRuleId(save.getId());
                    BeanUtils.copyProperties(h, houseExpenseRuleRef);
                    houseExpenseRuleRefRepository.save(houseExpenseRuleRef);
                });

                //
                switch (save.getChargeType()) {

                    case 1:
                        HouseExpenseWater houseExpenseWater = new HouseExpenseWater();
                        houseExpenseWater.setRuleId(save.getId());
                        houseExpenseWater.setWaterStartNum(createBillRequestParamVO.getStartNum());
                        houseExpenseWater.setWaterEndNum(createBillRequestParamVO.getEndNum());
                        houseExpenseWater.setWaterUsage(createBillRequestParamVO.getEndNum() - createBillRequestParamVO.getStartNum());
                        houseExpenseWaterRepository.save(houseExpenseWater);
                        break;
                    case 2:
                        HouseExpenseElectric houseExpenseElectric = new HouseExpenseElectric();
                        houseExpenseElectric.setRuleId(save.getId());
                        houseExpenseElectric.setElectricStartNum(createBillRequestParamVO.getStartNum());
                        houseExpenseElectric.setElectricEndNum(createBillRequestParamVO.getEndNum());
                        houseExpenseElectric.setElectricUsage(createBillRequestParamVO.getEndNum() - createBillRequestParamVO.getStartNum());
                        houseExpenseElectricRepository.save(houseExpenseElectric);
                        break;
                    case 6:
                        HouseExpenseGas houseExpenseGas = new HouseExpenseGas();
                        houseExpenseGas.setRuleId(save.getId());
                        houseExpenseGas.setGasStartNum(createBillRequestParamVO.getStartNum());
                        houseExpenseGas.setGasEndNum(createBillRequestParamVO.getEndNum());
                        houseExpenseGas.setGasUsage(createBillRequestParamVO.getEndNum() - createBillRequestParamVO.getStartNum());
                        houseExpenseGasRepository.save(houseExpenseGas);
                        break;
                }

            }
        } catch (Exception e) {
            LOG.error(e.getMessage(), e);
            result.setCode(StatusCode.ERROR_CODE_10001.getErrorCode());
            result.setMsg(e.getMessage());
        }
        return result;
    }

    /*
     *账单生成-编辑账单
     */
    public ResultBean<Object> updateBill(String accessToken, CreateBillRequestParamVO createBillRequestParamVO) {
        LOG.info("BillService updateBill accessToken={},createBillRequestParamVO={}", accessToken, createBillRequestParamVO);
        ResultBean<Object> result = new ResultBean<>();
        result.setMsg(StatusCode.SUCCESS_CODE_10000.getErrorMsg());
        result.setCode(StatusCode.SUCCESS_CODE_10000.getErrorCode());
        TokenRedisVo tokenRedisVo = redisService.findTokenVo(accessToken);
        try {
            if (tokenRedisVo == null) {
                result.setCode(StatusCode.ERROR_CODE_40001.getErrorCode());
                result.setMsg(StatusCode.ERROR_CODE_40001.getErrorMsg());
                return result;
            }
            HouseExpenseBillRuleVO houseExpenseBillRuleVO = createBillRequestParamVO.getHouseExpenseBillRuleVO();
            List<HouseExpenseRuleRefVO> houseExpenseRuleRefVOS = createBillRequestParamVO.getHouseExpenseRuleRefVOS();

            if (StringUtils.isBlank(houseExpenseBillRuleVO.getId())) {
                result.setMsg("账单id为空");
                return result;
            }

            // 查找是否存在收费项目，不存在则无法生成  [收费项目(1水费 2电费 3物业费 4宽带费 5电视费 6煤气费 7暖气费 8租金 9押金)]
            List<String> failRoomNumberList = new ArrayList<>(); // 不存在收费项目的房号
            if (houseExpenseBillRuleVO.getChargeType() == 8 || houseExpenseBillRuleVO.getChargeType() == 9) {
                Integer chargeType = null; // 收费类型：1房租 2押金
                if (houseExpenseBillRuleVO.getChargeType() == 8) {
                    chargeType = 1;
                } else {
                    chargeType = 2;
                }
                // 收费项目-房租/押金账单
                for (HouseExpenseRuleRefVO vo : houseExpenseRuleRefVOS) {
                    String houseId = vo.getHouseId();
                    List<HouseExpenseRent> list = houseExpenseRentRepository.findByHouseIdAndChargeTypeAndEnableStatusAndStatus(houseId, chargeType, 1, 1);
                    if (list.size() == 0) { // 不存在收费项目
                        failRoomNumberList.add(vo.getRoomNumber());
                    }
                }

            } else {

                // 收费项目-其它费用设置
                for (HouseExpenseRuleRefVO vo : houseExpenseRuleRefVOS) {
                    String houseId = vo.getHouseId();
                    List<HouseExpenseHouse> list = houseExpenseHouseRepository.findByHouseId(houseId);
                    // 项目费用id
                    List<String> expenseIdList = list.stream().map(HouseExpenseHouse::getExpenseId).collect(Collectors.toList());
                    // 绑定房的相关类型费用
                    List<Integer> chargeTypeList = houseExpenseOtherRepository.findByIdInAndEnableStatusAndStatus(expenseIdList, 1, 1).stream().map(HouseExpenseOther::getChargeType).collect(Collectors.toList());

                    if (!chargeTypeList.contains(houseExpenseBillRuleVO.getChargeType())) {  // 生成的类型不包含在收费项目里
                        failRoomNumberList.add(vo.getRoomNumber());
                    }

                }

            }
            // 返回失败房号
            if (failRoomNumberList.size() > 0) {
                return ResultBean.failedResultWithMsg(failRoomNumberList.toString() + "房号不存在收费项目，无法生成账单");
            }

            if (!houseExpenseBillRuleVO.getChargeType().equals(9)) {
                for (HouseExpenseRuleRefVO houseExpenseRuleRefVO : houseExpenseRuleRefVOS) {
                    List<HouseExpenseRuleRef> existHouseExpenseRule = houseExpenseRuleRefRepository.findByHouseId(houseExpenseRuleRefVO.getHouseId());
                    for (HouseExpenseRuleRef houseExpenseRuleRef : existHouseExpenseRule) {
                        if (houseExpenseRuleRef.getRuleId().equals(houseExpenseBillRuleVO.getId())) {
                            continue;
                        }
                        HouseExpenseBillRule houseExpenseBillRule = houseExpenseBillRuleRepository.findById(houseExpenseRuleRef.getRuleId());
                        // 时间段不能有交集
                        if (houseExpenseBillRule.getChargeType().equals(houseExpenseBillRuleVO.getChargeType())) {
                            if ((houseExpenseBillRuleVO.getStartTime().getTime() < houseExpenseBillRule.getStartTime().getTime() && houseExpenseBillRuleVO.getEndTime().getTime() >= houseExpenseBillRule.getStartTime().getTime())
                                    || (houseExpenseBillRuleVO.getStartTime().getTime() >= houseExpenseBillRule.getStartTime().getTime() && houseExpenseBillRuleVO.getEndTime().getTime() <= houseExpenseBillRule.getEndTime().getTime())
                                    || (houseExpenseBillRuleVO.getStartTime().getTime() <= houseExpenseBillRule.getEndTime().getTime() && houseExpenseBillRuleVO.getEndTime().getTime() > houseExpenseBillRule.getEndTime().getTime())) {
                                return ResultBean.failedResultWithMsg("周期内账单已存在");
                            }
                        }
                    }
                }
            } else {  // 押金

                for (HouseExpenseRuleRefVO houseExpenseRuleRefVO : houseExpenseRuleRefVOS) {
                    List<HouseExpenseRuleRef> existHouseExpenseRule = houseExpenseRuleRefRepository.findByHouseId(houseExpenseRuleRefVO.getHouseId());
                    for (HouseExpenseRuleRef houseExpenseRuleRef : existHouseExpenseRule) {
                        if (houseExpenseRuleRef.getRuleId().equals(houseExpenseBillRuleVO.getId())) {
                            continue;
                        }
                        HouseExpenseBillRule houseExpenseBillRule = houseExpenseBillRuleRepository.findById(houseExpenseRuleRef.getRuleId());
                        if (houseExpenseBillRule.getChargeType().equals(houseExpenseBillRuleVO.getChargeType())) {
                            return ResultBean.failedResultWithMsg("押金账单已存在");
                        }
                    }
                }
            }

            houseExpenseBillRuleRepository.deleteById(houseExpenseBillRuleVO.getId());
            houseExpenseRuleRefRepository.deleteByRuleId(houseExpenseBillRuleVO.getId());
            houseExpenseWaterRepository.deleteByRuleId(houseExpenseBillRuleVO.getId());
            houseExpenseElectricRepository.deleteByRuleId(houseExpenseBillRuleVO.getId());
            houseExpenseGasRepository.deleteByRuleId(houseExpenseBillRuleVO.getId());

            houseExpenseBillRuleVO.setId("ZD" + KeyWorker.nextId());
            houseExpenseBillRuleVO.setNumber(houseExpenseRuleRefVOS.size());
            houseExpenseBillRuleVO.setOperatorId(tokenRedisVo.getId());
            // houseExpenseBillRuleVO.setCreateTime(new Date());
            HouseExpenseBillRule houseExpenseBillRule = new HouseExpenseBillRule();
            BeanUtils.copyProperties(houseExpenseBillRuleVO, houseExpenseBillRule);
            HouseExpenseBillRule save = houseExpenseBillRuleRepository.save(houseExpenseBillRule);
            if (save != null) {
                houseExpenseRuleRefVOS.forEach(h -> {
                    HouseExpenseRuleRef houseExpenseRuleRef = new HouseExpenseRuleRef();
                    h.setCommunityId(save.getCommunityId());
                    h.setRuleId(save.getId());
                    BeanUtils.copyProperties(h, houseExpenseRuleRef);
                    houseExpenseRuleRefRepository.save(houseExpenseRuleRef);
                });

                //
                switch (save.getChargeType()) {

                    case 1:
                        HouseExpenseWater houseExpenseWater = new HouseExpenseWater();
                        houseExpenseWater.setRuleId(save.getId());
                        houseExpenseWater.setWaterStartNum(createBillRequestParamVO.getStartNum());
                        houseExpenseWater.setWaterEndNum(createBillRequestParamVO.getEndNum());
                        houseExpenseWater.setWaterUsage(createBillRequestParamVO.getEndNum() - createBillRequestParamVO.getStartNum());
                        houseExpenseWaterRepository.save(houseExpenseWater);
                        break;
                    case 2:
                        HouseExpenseElectric houseExpenseElectric = new HouseExpenseElectric();
                        houseExpenseElectric.setRuleId(save.getId());
                        houseExpenseElectric.setElectricStartNum(createBillRequestParamVO.getStartNum());
                        houseExpenseElectric.setElectricEndNum(createBillRequestParamVO.getEndNum());
                        houseExpenseElectric.setElectricUsage(createBillRequestParamVO.getEndNum() - createBillRequestParamVO.getStartNum());
                        houseExpenseElectricRepository.save(houseExpenseElectric);
                        break;
                    case 6:
                        HouseExpenseGas houseExpenseGas = new HouseExpenseGas();
                        houseExpenseGas.setRuleId(save.getId());
                        houseExpenseGas.setGasStartNum(createBillRequestParamVO.getStartNum());
                        houseExpenseGas.setGasEndNum(createBillRequestParamVO.getEndNum());
                        houseExpenseGas.setGasUsage(createBillRequestParamVO.getEndNum() - createBillRequestParamVO.getStartNum());
                        houseExpenseGasRepository.save(houseExpenseGas);
                        break;
                }


            }
        } catch (Exception e) {
            LOG.error(e.getMessage(), e);
            result.setCode(StatusCode.ERROR_CODE_10001.getErrorCode());
            result.setMsg(e.getMessage());
        }
        return result;
    }

    public ResultBean<Object> deleteBill(String accessToken, String id) {
        LOG.info("BillService deleteBill accessToken={},id={}", accessToken, id);

        if (StringUtils.isBlank(id)) {
            return ResultBean.failedResultWithMsg("账单id为空");
        }

        houseExpenseBillRuleRepository.deleteById(id);
        houseExpenseRuleRefRepository.deleteByRuleId(id);
        houseExpenseWaterRepository.deleteByRuleId(id);
        houseExpenseElectricRepository.deleteByRuleId(id);
        houseExpenseGasRepository.deleteByRuleId(id);

        return ResultBean.successfulResult(null);
    }

    public ResultBean<Object> billInfo(String accessToken, String id) {
        LOG.info("BillService billInfo accessToken={},id={}", accessToken, id);
        if (StringUtils.isBlank(id)) {
            return ResultBean.failedResultWithMsg("账单id为空");
        }
        HouseExpenseBillRule rule = houseExpenseBillRuleRepository.findById(id);
        if (rule == null) {
            return ResultBean.failedResultWithMsg("账单不存在");
        }

        HouseExpenseBillRuleVO vo = new HouseExpenseBillRuleVO();
        BeanUtils.copyProperties(rule, vo);

        // 获取小区名称
        Optional<Community> communityOptional = communityRepository.findById(vo.getCommunityId());

        if (communityOptional.isPresent()) {
            vo.setCommunityName(communityOptional.get().getName());
        }

        List<HouseExpenseRuleRef> ref = houseExpenseRuleRefRepository.findByRuleId(vo.getId());
        List<HouseExpenseRuleRefVO> refVO = new ArrayList<>();
        ref.forEach(r -> {
            HouseExpenseRuleRefVO v = new HouseExpenseRuleRefVO();
            BeanUtils.copyProperties(r, v);
            refVO.add(v);
        });

        vo.setHouseExpenseRuleRefVOList(refVO);

        switch (vo.getChargeType()) {
            case 1:
                HouseExpenseWater houseExpenseWater = houseExpenseWaterRepository.findByRuleId(vo.getId());
                vo.setStartNum(houseExpenseWater.getWaterStartNum());
                vo.setEndNum(houseExpenseWater.getWaterEndNum());
                break;
            case 2:
                HouseExpenseElectric houseExpenseElectric = houseExpenseElectricRepository.findByRuleId(vo.getId());
                vo.setStartNum(houseExpenseElectric.getElectricStartNum());
                vo.setEndNum(houseExpenseElectric.getElectricEndNum());
                break;
            case 6:
                HouseExpenseGas houseExpenseGas = houseExpenseGasRepository.findByRuleId(vo.getId());
                vo.setStartNum(houseExpenseGas.getGasStartNum());
                vo.setEndNum(houseExpenseGas.getGasEndNum());
                break;
        }

        return ResultBean.successfulResult(vo);
    }

    /*
     *收费项目-租金押金设置列表展示
     */
    public ResultBean<Object> rentProjectList(String accessToken, Integer pageNumber, Integer pageSize, String id) {
        LOG.info("EstateBillService rentProjectList accessToken={},pageNumber={},pageSize={},id={},type={}", accessToken, pageNumber, pageSize, id);
        ResultBean<Object> result = new ResultBean<>();
        result.setMsg(StatusCode.SUCCESS_CODE_10000.getErrorMsg());
        result.setCode(StatusCode.SUCCESS_CODE_10000.getErrorCode());
        TokenRedisVo tokenRedisVo = redisService.findTokenVo(accessToken);
        ApiData<Map<String, Object>> apiData = new ApiData<>();
        try {
            if (tokenRedisVo == null) {
                result.setCode(StatusCode.ERROR_CODE_40001.getErrorCode());
                result.setMsg(StatusCode.ERROR_CODE_40001.getErrorMsg());
                return result;
            }

            String billProjectSql = "select r.`id`,r.`name`,r.`charge_type` AS chargeType,r.`payment_stauts` AS paymentStauts,r.`house_id` AS houseId,\n" +
                    "r.`enable_status` AS enableStatus,r.`status`,r.`operator_id` AS operatorId,r.`operator_time` AS operatorTime,r.charge_mode AS chargeMode,\n" +
                    "r.price AS price from saas_house_expense_rent r LEFT JOIN saas_house h ON r.house_id = h.id WHERE r.status = 1 ";
            List<Object> args = new ArrayList<>();
            StringBuilder where = new StringBuilder();

            // 权限过滤
            List<Map<String, Object>> communityList = appCommunityService.findCommunity(accessToken).getResultData();
            if (communityList != null && !communityList.isEmpty()) {
                where.append(" and h.residence_community_id in " + SqlUtils.foreachIn(communityList.size()));
                List<String> communityIds = communityList.stream().map(map -> {
                    String comId = map.get("communityId").toString();
                    return comId;
                }).collect(Collectors.toList());
                args.addAll(communityIds);
            } else {
                where.append(" and h.residence_community_id in (null)");
            }

            String offsets = " LIMIT ? OFFSET ?";

            if (StringUtils.isNotBlank(id)) {
                where.append(" AND r.`id` = ?");
                args.add(id);
            }

            // 查询总记录数
            String count = "select count(1) FROM (" + billProjectSql + where + ")  c  ";
            Long total = jdbcTemplate.queryForObject(count, Long.class, args.toArray());
            LOG.info("SQL :" + billProjectSql + where);
            if (total == 0) {
                result.setCode(StatusCode.SUCCESS_CODE_10000.getErrorCode());
                result.setMsg(StatusCode.SUCCESS_CODE_10000.getErrorMsg());
                result.setResultData(null);
                return result;
            }
            // 查询分页数据
            where.append(" ORDER BY ").append(" r.operator_time desc ").append(" \n").append(offsets);
            args.add(pageSize);
            args.add(pageNumber * pageSize);
            List<Map<String, Object>> peopleList = jdbcTemplate.queryForList(billProjectSql + where, args.toArray());
            for (Map<String, Object> map : peopleList) {

                Object price = map.get("price");
                BigDecimal temp = null;
                if (price != null) {
                    temp = new BigDecimal(price.toString());
                }
                map.put("price", temp != null ? temp.divide(new BigDecimal(100)) : 0.00);

                String house = String.valueOf(map.get("houseId"));
                Optional<House> houseOptional = houseRepository.findById(house);
                String houseName = "";
                if (houseOptional.isPresent()) {
                    House existHouse = houseOptional.get();
                    if (existHouse != null) {
                        // 获取小区名称
                        Optional<Community> communityOptional = communityRepository.findById(existHouse.getResidenceCommunityId());
                        if (communityOptional.isPresent()) {
                            Community community = communityOptional.get();
                            houseName += community.getName();
                        }
                        // 获取楼栋名称
                        Optional<CommunityBuild> communityBuildOptional = communityBuildRepository.findById(existHouse.getBuildingNum());
                        if (communityBuildOptional.isPresent()) {
                            CommunityBuild communityBuild = communityBuildOptional.get();
                            houseName += communityBuild.getName() + "栋";
                        }
                        // 获取单元名称
                        Optional<CommunityBuildUnit> communityBuildUnitOptional = communityBuildUnitRepository.findById(existHouse.getUnit());
                        if (communityBuildUnitOptional.isPresent()) {
                            CommunityBuildUnit communityBuildUnit = communityBuildUnitOptional.get();
                            houseName += communityBuildUnit.getName() + "单元";
                        }
                        // 获取房号
                        houseName += existHouse.getRoomNumber() + "号";
                    }
                }
                map.put("houseName", houseName);
            }
            apiData.setData(peopleList);
            apiData.setTotal(total);
            LOG.info("SQL :" + billProjectSql + where);
            result.setResultData(apiData);
        } catch (Exception e) {
            LOG.error(e.getMessage(), e);
            result.setCode(StatusCode.ERROR_CODE_10001.getErrorCode());
            result.setMsg(e.getMessage());
        }
        return result;
    }

    /*
     *收费项目-其他费用设置列表展示
     */
    public ResultBean<Object> otherProjectList(String accessToken, Integer pageNumber, Integer pageSize, String id) {
        LOG.info("EstateBillService otherProjectList accessToken={},pageNumber={},pageSize={},id={},type={}", accessToken, pageNumber, pageSize, id);
        ResultBean<Object> result = new ResultBean<>();
        result.setMsg(StatusCode.SUCCESS_CODE_10000.getErrorMsg());
        result.setCode(StatusCode.SUCCESS_CODE_10000.getErrorCode());
        TokenRedisVo tokenRedisVo = redisService.findTokenVo(accessToken);
        ApiData<Map<String, Object>> apiData = new ApiData<>();
        try {
            if (tokenRedisVo == null) {
                result.setCode(StatusCode.ERROR_CODE_40001.getErrorCode());
                result.setMsg(StatusCode.ERROR_CODE_40001.getErrorMsg());
                return result;
            }

            String billProjectSql = " SELECT `id`,`name`,`charge_type` AS chargeType,`payment_stauts`  AS paymentStauts,`community_id` AS communityId,\n" +
                    "`charge_mode` AS chargeMode,`price`,`unit`,`billing_cycle_type` AS billingCycleType,`billing_cycle` AS billingCycle,\n" +
                    "`enable_status` AS enableStatus,`status`,`operator_id` AS operatorId,`operator_time` AS operatorTime FROM saas_house_expense_other WHERE status = 1 ";
            List<Object> args = new ArrayList<>();
            StringBuilder where = new StringBuilder();

            // 权限过滤
            List<Map<String, Object>> communityList = appCommunityService.findCommunity(accessToken).getResultData();
            if (communityList != null && !communityList.isEmpty()) {
                where.append(" and community_id in " + SqlUtils.foreachIn(communityList.size()));
                List<String> communityIds = communityList.stream().map(map -> {
                    String comId = map.get("communityId").toString();
                    return comId;
                }).collect(Collectors.toList());
                args.addAll(communityIds);
            } else {
                where.append(" and community_id in (null)");
            }

            String offsets = " LIMIT ? OFFSET ?";

            if (StringUtils.isNotBlank(id)) {
                where.append(" AND `id` = ?");
                args.add(id);
            }

            // 查询总记录数
            String count = "select count(1) FROM (" + billProjectSql + where + ")  c  ";
            Long total = jdbcTemplate.queryForObject(count, Long.class, args.toArray());
            LOG.info("SQL :" + billProjectSql + where);
            if (total == 0) {
                result.setCode(StatusCode.SUCCESS_CODE_10000.getErrorCode());
                result.setMsg(StatusCode.SUCCESS_CODE_10000.getErrorMsg());
                result.setResultData(null);
                return result;
            }

            // 查询分页数据
            where.append("  ORDER BY ").append("  operator_time desc ").append(" \n").append(offsets);
            args.add(pageSize);
            args.add(pageNumber * pageSize);

            List<Map<String, Object>> peopleList = jdbcTemplate.queryForList(billProjectSql + where, args.toArray());

            for (Map<String, Object> map : peopleList) {
                Integer price = Integer.valueOf(String.valueOf(map.get("price")));
                BigDecimal temp = new BigDecimal(price);
                map.put("price", temp != null ? temp.divide(new BigDecimal(100)) : 0.00);
                String communityId = String.valueOf(map.get("communityId"));
                // 获取小区名称
                Optional<Community> communityOptional = communityRepository.findById(communityId);
                if (communityOptional.isPresent()) {
                    Community community = communityOptional.get();
                    map.put("communityName", community.getName());
                }
            }

            apiData.setData(peopleList);
            apiData.setTotal(total);
            LOG.info("SQL :" + billProjectSql + where);
            result.setResultData(apiData);

        } catch (Exception e) {
            LOG.error(e.getMessage(), e);
            result.setCode(StatusCode.ERROR_CODE_10001.getErrorCode());
            result.setMsg(e.getMessage());
        }
        return result;
    }

    /*
     *租金押金项目或其他费用项目
     *状态操作
     * status 0 禁用 1 启用 2 删除
     */
    public ResultBean<Object> changeRentOrOtherProjectStatus(String accessToken, String id, Integer status, Integer type) {
        LOG.info("payEstateBillService changeRentProjectStatus accessToken={},id={},status={},type={}", accessToken, id, status, type);
        ResultBean<Object> result = new ResultBean<>();
        result.setMsg(StatusCode.SUCCESS_CODE_10000.getErrorMsg());
        result.setCode(StatusCode.SUCCESS_CODE_10000.getErrorCode());
        TokenRedisVo tokenRedisVo = redisService.findTokenVo(accessToken);
        try {
            if (tokenRedisVo == null) {
                result.setCode(StatusCode.ERROR_CODE_40001.getErrorCode());
                result.setMsg(StatusCode.ERROR_CODE_40001.getErrorMsg());
                return result;
            }
            if (id == null || status == null || type == null) {
                result.setCode(StatusCode.ERROR_CODE_10005.getErrorCode());
                result.setMsg(StatusCode.ERROR_CODE_10005.getErrorMsg());
                return result;
            }
            if (type == 0) {
                HouseExpenseRent houseExpenseRent = houseExpenseRentRepository.findById(id);
                if (status == 2) {
                    houseExpenseRent.setStatus(status);
                } else {
                    if (status == 1) {
                        List<HouseExpenseRent> houseExpenseRentList = houseExpenseRentRepository.findByHouseIdAndChargeTypeAndEnableStatusAndStatus(houseExpenseRent.getHouseId(), houseExpenseRent.getChargeType(), 1, 1);
                        if (houseExpenseRentList != null && houseExpenseRentList.size() > 0) {
                            return ResultBean.failedResultWithMsg("房屋此类型收费项目已有开启,更换需禁用之前收费项目再开启当前收费项目");
                        }
                    }
                    houseExpenseRent.setEnableStatus(status);
                }
                houseExpenseRentRepository.save(houseExpenseRent);
            } else {
                HouseExpenseOther expenseOther = houseExpenseOtherRepository.findById(id);
                if (status == 2) {
                    expenseOther.setStatus(status);
                } else {
                    if (status == 1) {
                        List<HouseExpenseOther> existHouseExpenseOther = houseExpenseOtherRepository.findByCommunityIdAndChargeTypeAndEnableStatusAndStatus(expenseOther.getCommunityId(), expenseOther.getChargeType(), 1, 1);
                        if (existHouseExpenseOther != null && existHouseExpenseOther.size() > 0) {
                            return ResultBean.failedResultWithMsg("小区此类型收费项目已有开启,更换需禁用之前收费项目再开启当前收费项目");
                        }
                    }
                    expenseOther.setEnableStatus(status);
                }
                //
                houseExpenseOtherRepository.save(expenseOther);

            }
        } catch (Exception e) {
            LOG.error(e.getMessage(), e);
            result.setCode(StatusCode.ERROR_CODE_10001.getErrorCode());
            result.setMsg(e.getMessage());
        }
        return result;
    }

    /*
     *其他费用设置绑定房产
     */
    public ResultBean<Object> otherSetBindingHouse(String accessToken, List<BindingHouseRequestVO> bindingHouseRequestVOList) {
        LOG.info("EstateBillService otherSetBindingHouse accessToken={},bindingHouseRequestVO={}", accessToken, bindingHouseRequestVOList);
        ResultBean<Object> result = new ResultBean<>();
        result.setMsg(StatusCode.SUCCESS_CODE_10000.getErrorMsg());
        result.setCode(StatusCode.SUCCESS_CODE_10000.getErrorCode());
        TokenRedisVo tokenRedisVo = redisService.findTokenVo(accessToken);
        try {
            if (tokenRedisVo == null) {
                result.setCode(StatusCode.ERROR_CODE_40001.getErrorCode());
                result.setMsg(StatusCode.ERROR_CODE_40001.getErrorMsg());
                return result;
            }

            if (bindingHouseRequestVOList == null) {
                result.setCode(StatusCode.ERROR_CODE_10005.getErrorCode());
                result.setMsg(StatusCode.ERROR_CODE_10005.getErrorMsg());
                return result;
            }

            for (BindingHouseRequestVO requestVO : bindingHouseRequestVOList) {
                HouseExpenseHouse expenseHouse = houseExpenseHouseRepository.findByHouseIdAndExpenseId(requestVO.getHouseId(), requestVO.getId());
                Optional<House> houseOptional = houseRepository.findById(requestVO.getHouseId());
                if (expenseHouse != null) {
                    if (houseOptional.isPresent()) {
                        House house = houseOptional.get();
                        return ResultBean.failedResultWithMsg(house.getRoomNumber() + "房已存在当前类型费用项目");
                    }
                }
            }

            for (BindingHouseRequestVO requestVO : bindingHouseRequestVOList) {
                HouseExpenseHouse houseExpenseHouse = new HouseExpenseHouse();
                houseExpenseHouse.setHouseId(requestVO.getHouseId());
                houseExpenseHouse.setExpenseId(requestVO.getId());
                houseExpenseHouse.setHouseNature(requestVO.getHouseNature());
                houseExpenseHouseRepository.save(houseExpenseHouse);
            }

        } catch (Exception e) {
            LOG.error(e.getMessage(), e);
            result.setCode(StatusCode.ERROR_CODE_10001.getErrorCode());
            result.setMsg(e.getMessage());
        }
        return result;
    }

    /*
     *新增或修改租金押金项目
     */
    public ResultBean<Object> addOrUpdateRentProject(String accessToken, List<HouseExpenseRentVO> houseExpenseRentVOList) {
        LOG.info("EstateBillService addOrUpdateRentProject accessToken={},houseExpenseRentVO={}", accessToken, houseExpenseRentVOList.toString());

        ResultBean<Object> result = new ResultBean<>();
        result.setMsg(StatusCode.SUCCESS_CODE_10000.getErrorMsg());
        result.setCode(StatusCode.SUCCESS_CODE_10000.getErrorCode());
        TokenRedisVo tokenRedisVo = redisService.findTokenVo(accessToken);
        try {
            if (tokenRedisVo == null) {
                result.setCode(StatusCode.ERROR_CODE_40001.getErrorCode());
                result.setMsg(StatusCode.ERROR_CODE_40001.getErrorMsg());
                return result;
            }
            if (houseExpenseRentVOList == null) {
                result.setCode(StatusCode.ERROR_CODE_10005.getErrorCode());
                result.setMsg(StatusCode.ERROR_CODE_10005.getErrorMsg());
                return result;
            }
            for (HouseExpenseRentVO houseExpenseRentVO : houseExpenseRentVOList) {
                // 新增操作
                if (houseExpenseRentVO.getId() == null) {

                    List<HouseExpenseRent> houseExpenseRentList = houseExpenseRentRepository.findByHouseIdAndChargeTypeAndStatus(houseExpenseRentVO.getHouseId(), houseExpenseRentVO.getChargeType(), 1);
                    if (houseExpenseRentList.size() > 0) {
                        return ResultBean.failedResultWithMsg("收费项目已存在");
                    }

                    houseExpenseRentVO.setId("PR" + KeyWorker.nextId());
                    houseExpenseRentVO.setEnableStatus(0);
                    houseExpenseRentVO.setStatus(1);
                    houseExpenseRentVO.setOperatorId(tokenRedisVo.getId());
                    houseExpenseRentVO.setOperatorTime(new Date());
                    HouseExpenseRent houseExpenseRent = new HouseExpenseRent();

                    String price = yuanToFen(String.valueOf(houseExpenseRentVO.getPrice()));
                    houseExpenseRentVO.setPrice(new BigDecimal(price));

                    BeanUtils.copyProperties(houseExpenseRentVO, houseExpenseRent);

                    houseExpenseRent.setPrice(Integer.valueOf(price));

                    houseExpenseRentRepository.save(houseExpenseRent);
                } else {
                    // 修改操作
                    HouseExpenseRent houseExpenseRent = houseExpenseRentRepository.findById(houseExpenseRentVO.getId());

                    if (!houseExpenseRent.getHouseId().equals(houseExpenseRentVO.getHouseId())) {

                        List<HouseExpenseRent> houseExpenseRentList = houseExpenseRentRepository.findByHouseIdAndChargeTypeAndStatus(houseExpenseRentVO.getHouseId(), houseExpenseRentVO.getChargeType(), 1);
                        if (houseExpenseRentList.size() > 0) {
                            return ResultBean.failedResultWithMsg("收费项目已存在");
                        }
                    }

                    houseExpenseRent.setName(houseExpenseRentVO.getName());
                    houseExpenseRent.setPaymentStauts(houseExpenseRentVO.getPaymentStauts());
                    houseExpenseRent.setHouseId(houseExpenseRentVO.getHouseId());
                    houseExpenseRent.setOperatorId(tokenRedisVo.getId());
                    houseExpenseRent.setOperatorTime(new Date());

                    String price = yuanToFen(String.valueOf(houseExpenseRentVO.getPrice()));
                    houseExpenseRent.setChargeMode(houseExpenseRentVO.getChargeMode());
                    houseExpenseRent.setPrice(Integer.valueOf(price));

                    houseExpenseRentRepository.save(houseExpenseRent);
                }
            }
        } catch (Exception e) {
            LOG.error(e.getMessage(), e);
            result.setCode(StatusCode.ERROR_CODE_10001.getErrorCode());
            result.setMsg(e.getMessage());
        }
        return result;
    }

    /*
     *新增或修改其他费用项目
     */
    public ResultBean<Object> addOrUpdateOtherProject(String accessToken, HouseExpenseOtherVO houseExpenseOtherVO) {
        LOG.info("EstateBillService addOrUpdateOtherProject accessToken={},houseExpenseOtherVO={}", accessToken, houseExpenseOtherVO);
        ResultBean<Object> result = new ResultBean<>();
        result.setMsg(StatusCode.SUCCESS_CODE_10000.getErrorMsg());
        result.setCode(StatusCode.SUCCESS_CODE_10000.getErrorCode());
        TokenRedisVo tokenRedisVo = redisService.findTokenVo(accessToken);
        try {
            if (tokenRedisVo == null) {
                result.setCode(StatusCode.ERROR_CODE_40001.getErrorCode());
                result.setMsg(StatusCode.ERROR_CODE_40001.getErrorMsg());
                return result;
            }
            if (houseExpenseOtherVO == null) {
                result.setCode(StatusCode.ERROR_CODE_10005.getErrorCode());
                result.setMsg(StatusCode.ERROR_CODE_10005.getErrorMsg());
                return result;
            }
            // 新增操作
            if (houseExpenseOtherVO.getId() == null) {

                List<HouseExpenseOther> houseExpenseOtherList = houseExpenseOtherRepository.findByCommunityIdAndChargeTypeAndStatus(houseExpenseOtherVO.getCommunityId(), houseExpenseOtherVO.getChargeType(), 1);
                if (houseExpenseOtherList.size() > 0) {
                    return ResultBean.failedResultWithMsg("收费项目已存在");
                }

                houseExpenseOtherVO.setId("PO" + KeyWorker.nextId());
                houseExpenseOtherVO.setEnableStatus(0);
                houseExpenseOtherVO.setStatus(1);
                String price = yuanToFen(String.valueOf(houseExpenseOtherVO.getPrice()));
                houseExpenseOtherVO.setOperatorId(tokenRedisVo.getId());
                houseExpenseOtherVO.setOperatorTime(new Date());
                HouseExpenseOther houseExpenseOther = new HouseExpenseOther();
                houseExpenseOther.setPrice(Integer.valueOf(price));
                BeanUtils.copyProperties(houseExpenseOtherVO, houseExpenseOther);
                HouseExpenseOther save = houseExpenseOtherRepository.save(houseExpenseOther);

            } else {
                // 修改操作
                HouseExpenseOther houseExpenseOther = houseExpenseOtherRepository.findById(houseExpenseOtherVO.getId());

                if ((!houseExpenseOther.getCommunityId().equals(houseExpenseOtherVO.getCommunityId())) ||
                        (!houseExpenseOther.getChargeType().equals(houseExpenseOtherVO.getChargeType()))) {

                    List<HouseExpenseOther> houseExpenseOtherList = houseExpenseOtherRepository.findByCommunityIdAndChargeTypeAndStatus(houseExpenseOtherVO.getCommunityId(), houseExpenseOtherVO.getChargeType(), 1);
                    if (houseExpenseOtherList.size() > 0) {
                        return ResultBean.failedResultWithMsg("收费项目已存在");
                    }
                }

                houseExpenseOther.setName(houseExpenseOtherVO.getName());
                houseExpenseOther.setPaymentStauts(houseExpenseOtherVO.getPaymentStauts());
                houseExpenseOther.setChargeMode(houseExpenseOtherVO.getChargeMode());
                houseExpenseOther.setCommunityId(houseExpenseOtherVO.getCommunityId());
                String price = yuanToFen(String.valueOf(houseExpenseOtherVO.getPrice()));
                houseExpenseOther.setPrice(Integer.valueOf(price));
                houseExpenseOther.setUnit(houseExpenseOtherVO.getUnit());
                houseExpenseOther.setBillingCycleType(houseExpenseOtherVO.getBillingCycleType());
                houseExpenseOther.setBillingCycle(houseExpenseOtherVO.getBillingCycle());
                houseExpenseOther.setOperatorId(tokenRedisVo.getId());
                houseExpenseOther.setOperatorTime(new Date());
                HouseExpenseOther save = houseExpenseOtherRepository.save(houseExpenseOther);

            }
        } catch (Exception e) {
            LOG.error(e.getMessage(), e);
            result.setCode(StatusCode.ERROR_CODE_10001.getErrorCode());
            result.setMsg(e.getMessage());
        }
        return result;
    }

    /**
     * 收支登记
     *
     * @param accessToken
     * @param pageNumber
     * @param pageSize
     * @param filter      权限范围内的所有收支明细
     */
    public ResultBean<ApiData<IncomeAndExpenditureVO>> incomeAndExpenditure(String accessToken, Integer pageNumber, Integer pageSize, BillSearchRequestVO filter) {
        ResultBean<ApiData<IncomeAndExpenditureVO>> resultBean = new ResultBean<>();
        resultBean.setCode("10000");
        resultBean.setMsg("10000");
        ApiData<IncomeAndExpenditureVO> apiData = new ApiData<>();
        resultBean.setResultData(apiData);
        try {
            StringBuilder where = new StringBuilder();
            List<Object> args = new ArrayList<>();
            where.append(" where 1 = 1");

            String sql = " FROM (\n" +
                    "SELECT b.`id`,b.house_id as houseId,b.`community_id` AS communityId,b.`building_num` AS buildingNum,\n" +
                    "b.`unit`,b.`room_number` AS roomNumber,b.`match_user_id` AS matchUserId,CASE WHEN b.charge_type = 1 THEN 8 ELSE 9 END  AS chargeType,\n" +
                    "b.`cost`,b.`payment_time` AS paymentTime,1 AS incomeAndExpenditure,h.floor,a1.name as name1,a2.name as name2\n" +
                    "FROM `saas_estate_rent_bill` b \n" +
                    "LEFT JOIN `saas_app_user` a1 ON a1.`id` = b.match_user_id \n" +
                    "LEFT JOIN `saas_app_user` a2 ON a2.`enterprise_user_id` = b.match_user_id \n" +
                    "left join `saas_house` h on b.house_id = h.id\n" +
                    "WHERE b.payment_status = 1 \n" +
                    "UNION ALL SELECT  b.`id`,b.house_id as houseId,b.`community_id` AS communityId,b.`building_num` AS buildingNum,\n" +
                    "b.`unit`,b.`room_number` AS roomNumber,b.`match_user_id` AS matchUserId,b.`charge_type` AS chargeType,\n" +
                    "b.`cost`,b.`payment_time` AS paymentTime,1 AS incomeAndExpenditure,h.floor,a1.name as name1,a2.name as name2 \n" +
                    "FROM `saas_estate_expense_bill` b \n" +
                    "LEFT JOIN `saas_app_user` a1 ON a1.`id` = b.match_user_id \n" +
                    "LEFT JOIN `saas_app_user` a2 ON a2.`enterprise_user_id` = b.match_user_id \n" +
                    "left join `saas_house` h on b.house_id = h.id\n" +
                    "WHERE b.payment_status = 1 ) a";

            // 权限过滤
            List<Map<String, Object>> communityList = appCommunityService.findCommunity(accessToken).getResultData();
            if (communityList != null && !communityList.isEmpty()) {
                where.append(" and a.communityId in " + SqlUtils.foreachIn(communityList.size()));
                List<String> communityIds = communityList.stream().map(map -> {
                    String comId = map.get("communityId").toString();
                    return comId;
                }).collect(Collectors.toList());
                args.addAll(communityIds);
            } else {
                where.append(" and a.communityId in (null)");
            }

            if (StringUtils.isNotBlank(filter.getCommunityId())) {
                where.append(" AND a.`communityId` = ?\n");
                args.add(filter.getCommunityId());
            }
            if (filter.getBuildingNum() != null) {
                where.append(" AND a.`buildingNum` = ?\n");
                args.add(filter.getBuildingNum());
            }
            if (filter.getUnit() != null) {
                where.append(" AND a.`unit` = ?\n");
                args.add(filter.getUnit());
            }
            if (StringUtils.isNotBlank(filter.getRoomNumber())) {
                where.append(" AND a.`roomNumber` = ?\n");
                args.add(filter.getRoomNumber());
            }
            if (filter.getFloor() != null) {
                where.append(" AND a.`floor` = ?\n");
                args.add(filter.getFloor());
            }
            if (filter.getCost() != null) {
                where.append(" AND a.`cost` = ?\n");
                args.add(ConvertUtils.yuan2fen(filter.getCost()));
            }
            if (StringUtils.isNotBlank(filter.getMatchUserName())) {
                where.append(" AND (a.`name1` = ? or a.`name2` = ?) \n");
                args.add(filter.getMatchUserName());
                args.add(filter.getMatchUserName());
            }
            if (filter.getChargeType() != null) {
                where.append(" AND a.`chargeType` = ?\n");
                args.add(filter.getChargeType());
            }
            if (filter.getIncomeAndExpenditure() != null) {
                where.append(" AND a.`incomeAndExpenditure` = ?\n");
                args.add(filter.getIncomeAndExpenditure());
            }

            // 总记录数
            Long total = jdbcTemplate.queryForObject(IAE_COUNT + sql + where, args.toArray(), Long.class);
            if (total == 0) {
                return resultBean;
            }

            args.add(pageSize);
            args.add(pageNumber * pageSize);
            List<IncomeAndExpenditureVO> list = jdbcTemplate.query(IAE_COLUM + sql + where + ORDER_ORDER + SQL_PAGE,
                    new BeanPropertyRowMapper<>(IncomeAndExpenditureVO.class), args.toArray());

            // 查询小区名称
            List<String> rcIds = list.stream().filter(vo -> vo.getCommunityId() != null).map(IncomeAndExpenditureVO::getCommunityId).distinct().collect(Collectors.toList());
            if (!rcIds.isEmpty()) {
                Map<String, String> map = communityRepository.findByIdIn(rcIds).stream().collect(Collectors.toMap(Community::getId, Community::getName));
                if (!map.isEmpty()) {
                    list.forEach(vo -> vo.setCommunityName(map.get(vo.getCommunityId())));
                }
            }
            // 查询楼栋名称
            List<Long> buildingIds = list.stream().filter(vo -> vo.getBuildingNum() != null).map(IncomeAndExpenditureVO::getBuildingNum).distinct().collect(Collectors.toList());
            if (!buildingIds.isEmpty()) {
                Map<Long, String> map = communityBuildRepository.findByIdIn(buildingIds).stream().filter(vo -> vo.getName() != null).collect(Collectors.toMap(CommunityBuild::getId, CommunityBuild::getName));
                if (!map.isEmpty()) {
                    list.forEach(vo -> vo.setBuildingNumName(map.get(vo.getBuildingNum())));
                }
            }
            // 查询单元名称
            List<Long> unitIds = list.stream().filter(vo -> vo.getUnit() != null).map(IncomeAndExpenditureVO::getUnit).distinct().collect(Collectors.toList());
            if (!unitIds.isEmpty()) {
                Map<Long, String> map = communityBuildUnitRepository.findByIdIn(unitIds).stream().collect(Collectors.toMap(CommunityBuildUnit::getId, CommunityBuildUnit::getName));
                if (!map.isEmpty()) {
                    list.forEach(vo -> vo.setUnitName(map.get(vo.getUnit())));
                }
            }
            // 查询经手人名称
            String applySql = "select b.name ,c.phone from  saas_house_match_user a \n" +
                    "LEFT JOIN saas_house_eligible_apply b on b.id = a.apply_id\n" +
                    "LEFT JOIN saas_app_user c on c.id = a.match_user_id \n" +
                    "where a.house_id=? and a.status=1 ";
            for (IncomeAndExpenditureVO vo : list) {
                List<CommunityHouseUser> communityHouseUserList = communityHouseUserRepository.findByCommunityHouseIdAndStatusAndType(vo.getHouseId(), 1, 1);
                // 承租人
                if (communityHouseUserList != null && communityHouseUserList.size() > 0) {
                    vo.setMatchUserName(communityHouseUserList.get(0).getName());
                } else {
                    List<AppUser> appUser = jdbcTemplate.query(applySql, new BeanPropertyRowMapper<>(AppUser.class), vo.getHouseId());
                    if (appUser != null && appUser.size() > 0) {
                        vo.setMatchUserName(appUser.get(0).getName());
                    }
                }
            }


//            List<String> userIds = list.stream().filter(vo -> vo.getMatchUserId() != null).map(IncomeAndExpenditureVO::getMatchUserId).distinct().collect(Collectors.toList());
//            if (!userIds.isEmpty()) {
//                List<AppUser> appUsers = appUserRepository.findByIdOrEnterpriseIn(userIds, userIds);
//                for (AppUser appUser : appUsers) {
//                    for (IncomeAndExpenditureVO vo : list) {
//                        if (vo.getMatchUserId().equals(appUser.getId()) || vo.getMatchUserId().equals(appUser.getEnterpriseUserId())) {
//                            vo.setMatchUserName(appUser.getName());
//                        }
//                    }
//                }
//            }

            for (IncomeAndExpenditureVO vo : list) {
                // 经手人
                vo.setPaymentTime(DateUtils.format(vo.getPaymentTime(), DateUtils.FORMAT_DATETIME_14, DateUtils.FORMAT_DATETIME_14));
                vo.setCost(ConvertUtils.fen2yuan(vo.getCost().intValue()));
            }

            apiData.setData(list);
            apiData.setTotal(total);

            resultBean.setResultData(apiData);
        } catch (Exception e) {
            LOG.error(e.getMessage(), e);
            resultBean.setCode("10001");
            resultBean.setMsg("10001");
        }
        resultBean.setResultData(apiData);
        return resultBean;
    }

    /*
     *分转换为元
     */
    private String fenToYuan(String amount) {
        NumberFormat format = NumberFormat.getInstance();
        try {
            Number number = format.parse(amount);
            double temp = number.doubleValue() / 100.0;
            format.setGroupingUsed(false);
            // 设置返回的小数部分所允许的最大位数
            format.setMaximumFractionDigits(2);
            amount = format.format(temp);
        } catch (ParseException e) {
            e.printStackTrace();
        }
        return amount;
    }

    /*
     *元转换为分
     */
    private String yuanToFen(String amount) {
        NumberFormat format = NumberFormat.getInstance();
        try {
            Number number = format.parse(amount);
            double temp = number.doubleValue() * 100.0;
            format.setGroupingUsed(false);
            // 设置返回数的小数部分所允许的最大位数
            format.setMaximumFractionDigits(0);
            amount = format.format(temp);
        } catch (ParseException e) {
            e.printStackTrace();
        }
        return amount;
    }

    public ResultBean<Object> findBindingHouse(String accessToken, String id) {
        try {
            if (StringUtils.isBlank(id)) {
                return ResultBean.failedResultOfParamWithMsg("项目id为空");
            }

            List<String> houseIds = houseExpenseHouseRepository.findByExpenseId(id).stream()
                    .map(HouseExpenseHouse::getHouseId).distinct().collect(Collectors.toList());

            return ResultBean.successfulResult(houseIds);
        } catch (Exception e) {
            LOG.error(e.getMessage(), e);
            return ResultBean.failedResultOfException();
        }
    }


    /**
     * 公租房租金/押金待办事项
     *
     * @param houseId     房屋ID
     * @param matchUserId 承租人ID
     * @param cost        金额
     * @param rentBillId  账单ID
     * @param operatorId  操作人
     * @param type        收费项目:1租金 2押金
     */
    public void rentBillBacklogItem(String houseId, String matchUserId, Integer cost, Long rentBillId, String operatorId, Integer type) {
        String yuan = fenToYuan(String.valueOf(cost));
        String typeName = type == 1 ? "租金" : "押金";

        Optional<AppUser> optional = appUserRepository.findById(matchUserId);
        // 生成租金待办事项
        BacklogItem backlogItem = new BacklogItem();
        backlogItem.setHouseId(houseId);
        backlogItem.setType(BacklogItemTypeEnum.HOUSE_BILL_RENT.getValue());
        backlogItem.setTitle(typeName + "账单待缴费");
        backlogItem.setContent(typeName + "账单，请尽快缴费");
        backlogItem.setBusinessStatus("待缴费");
        backlogItem.setHandleStatus(0);
        backlogItem.setOperatorId(operatorId);// 操作人id
        backlogItem.setMatchUserId(matchUserId);
        if (optional.isPresent()) {
            AppUser appUser = optional.get();
            backlogItem.setPhone(appUser.getPhone());
        }
        backlogItem.setCreateTime(new Date());
        BacklogItem save = backlogItemRepository.save(backlogItem);

        BacklogItemContractBill contractBill = new BacklogItemContractBill();
        contractBill.setBacklogItemId(save.getId());
        contractBill.setAmount(cost);// 合计金额
        contractBill.setMatchUserId(matchUserId);
        if (type == 1) {
            contractBill.setRentBillId(rentBillId);// 租金账单id
        } else {
            contractBill.setDepositBillId(rentBillId);// 押金账单id
        }
        backlogItemContractBillRepository.save(contractBill);

        if (optional.isPresent()) {
            AppUser appUser = optional.get();
            // 账单生成短信
            smsService.sendTemplateSmsWithData(appUser.getPhone(), smsConfig.getBillTemplateCode(), new String[]{typeName, yuan});
        }


        // 根据房屋ID和承租人ID，获得申请ID
        List<HouseMatchUser> houseMatchUserList = houseMatchUserRepository.findByHouseIdAndMatchUserIdAndStatus(houseId, matchUserId, 1);
        if (null != houseMatchUserList && houseMatchUserList.size() > 0) {
            List<Long> applyIdList = houseMatchUserList.stream().map(x -> x.getApplyId()).collect(Collectors.toList());
            List<Cohabit> cohabitList = cohabitRepository.findByApplyIdIn(applyIdList);

            for (Cohabit cohabit : cohabitList) {
                if (StringUtils.isEmpty(cohabit.getMobile())) {
                    continue;
                }
                AppUser appUser = appUserRepository.findByPhoneAndTypeAndStatus(cohabit.getMobile(),UserLoginTypeEnum.CONSUMER.getValue(), 1);

                // 生成租金/押金待办事项
                BacklogItem backlogItems = new BacklogItem();
                backlogItems.setHouseId(houseId);
                backlogItems.setType(BacklogItemTypeEnum.HOUSE_BILL_RENT.getValue());
                backlogItems.setTitle(typeName + "账单待缴费");
                backlogItems.setContent(typeName + "账单，请尽快缴费");
                backlogItems.setBusinessStatus("待缴费");
                backlogItems.setHandleStatus(0);
                backlogItems.setOperatorId(operatorId);// 操作人id
                if (appUser != null) {
                    backlogItems.setMatchUserId(appUser.getId());
                }
                backlogItems.setPhone(cohabit.getMobile());
                backlogItems.setCreateTime(new Date());
                BacklogItem saves = backlogItemRepository.save(backlogItems);

                BacklogItemContractBill contractBills = new BacklogItemContractBill();
                contractBills.setBacklogItemId(saves.getId());
                contractBills.setAmount(cost);// 合计金额
                if (appUser != null) {
                    contractBills.setMatchUserId(appUser.getId());
                }
                if (type == 1) {
                    contractBill.setRentBillId(rentBillId);// 租金账单id
                } else {
                    contractBill.setDepositBillId(rentBillId);// 押金账单id
                }
                backlogItemContractBillRepository.save(contractBills);

                // 账单生成短信
                smsService.sendTemplateSmsWithData(cohabit.getMobile(), smsConfig.getBillTemplateCode(), new String[]{typeName, yuan});

            }
        }
    }


    /**
     * 生成公租房其他费用账单待办事项
     *
     * @param houseId     房屋ID
     * @param matchUserId 承租人ID
     * @param rentBillId  账单ID
     * @param chargeType  收费类型 1水费 2电费 3物业费 4宽带费 5电视费 6煤气费 7暖气费
     * @param cost        金额
     * @param operatorId  操作人ID
     */
    public void houseOtherBillItem(String houseId, String matchUserId, Long rentBillId, Integer chargeType, Integer cost, String operatorId) {
        String yuan = fenToYuan(String.valueOf(cost));
        String chargeName = null;
        switch (chargeType) {  // 1水费 2电费 3物业费 4宽带费 5电视费 6煤气费 7暖气费
            case 1:
                chargeName = "水费";
                break;
            case 2:
                chargeName = "电费";
                break;
            case 3:
                chargeName = "物业费";
                break;
            case 4:
                chargeName = "宽带费";
                break;
            case 5:
                chargeName = "电视费";
                break;
            case 6:
                chargeName = "燃气费";
                break;
            case 7:
                chargeName = "暖气费";
                break;
        }

        Optional<AppUser> optional = appUserRepository.findById(matchUserId);
        // 生成公租房其他费用账单待办事项
        BacklogItem backlogItem = new BacklogItem();
        backlogItem.setHouseId(houseId);
        backlogItem.setType(BacklogItemTypeEnum.HOUSE_OTHER_BILL.getValue());
        backlogItem.setTitle(chargeName + "账单待缴费");
        backlogItem.setContent(chargeName + "账单，请尽快缴费");
        backlogItem.setBusinessStatus("待缴费");
        backlogItem.setHandleStatus(0);
        backlogItem.setOperatorId(operatorId);// 操作人id
        backlogItem.setMatchUserId(matchUserId);
        if (optional.isPresent()) {
            AppUser appUser = optional.get();
            backlogItem.setPhone(appUser.getPhone());
        }
        backlogItem.setCreateTime(new Date());
        BacklogItem save = backlogItemRepository.save(backlogItem);

        BacklogItemHouseOtherBill houseOtherBill = new BacklogItemHouseOtherBill();
        houseOtherBill.setBacklogItemId(save.getId());// 待办事项id
        houseOtherBill.setBillId(rentBillId);// 账单id
        backlogItemHouseOtherBillRepository.save(houseOtherBill);

        if (optional.isPresent()) {
            AppUser appUser = optional.get();
            // 账单生成短信
            smsService.sendTemplateSmsWithData(appUser.getPhone(), smsConfig.getBillTemplateCode(), new String[]{chargeName, yuan});
        }

        // 根据房屋ID和承租人ID，获得申请ID
        List<HouseMatchUser> houseMatchUserList = houseMatchUserRepository.findByHouseIdAndMatchUserIdAndStatus(houseId, matchUserId, 1);
        if (null != houseMatchUserList && houseMatchUserList.size() > 0) {
            List<Long> applyIdList = houseMatchUserList.stream().map(x -> x.getApplyId()).collect(Collectors.toList());
            List<Cohabit> cohabitList = cohabitRepository.findByApplyIdIn(applyIdList);

            for (Cohabit cohabit : cohabitList) {
                if (StringUtils.isEmpty(cohabit.getMobile())) {
                    continue;
                }
                AppUser appUser = appUserRepository.findByPhoneAndTypeAndStatus(cohabit.getMobile(),UserLoginTypeEnum.CONSUMER.getValue(), 1);

                BacklogItem backlogItems = new BacklogItem();
                backlogItems.setHouseId(houseId);
                backlogItems.setType(BacklogItemTypeEnum.HOUSE_OTHER_BILL.getValue());
                backlogItems.setTitle(chargeName + "账单待缴费");
                backlogItems.setContent(chargeName + "账单，请尽快缴费");
                backlogItems.setBusinessStatus("待缴费");
                backlogItems.setHandleStatus(0);
                backlogItems.setOperatorId(operatorId);// 操作人id
                if (appUser != null) {
                    backlogItems.setMatchUserId(appUser.getId());
                }
                backlogItems.setPhone(cohabit.getMobile());
                backlogItems.setCreateTime(new Date());
                BacklogItem saves = backlogItemRepository.save(backlogItem);

                BacklogItemHouseOtherBill houseOtherBills = new BacklogItemHouseOtherBill();
                houseOtherBills.setBacklogItemId(saves.getId());
                houseOtherBills.setBillId(rentBillId);// 租金账单id
                backlogItemHouseOtherBillRepository.save(houseOtherBills);

                // 账单生成短信
                smsService.sendTemplateSmsWithData(cohabit.getMobile(), smsConfig.getBillTemplateCode(), new String[]{chargeName, yuan});

            }
        }
    }


    /**
     * 非公租房租金/押金待办事项
     *
     * @param houseId        房屋ID
     * @param rentBillId     账单ID
     * @param billChargeType 收费项目:1租金 2押金
     * @param cost           金额
     * @param operatorId     操作人
     */
    public void propertyRentItem(String houseId, Long rentBillId, Integer billChargeType, Integer cost, String operatorId, EstateRentBill rentBill) {

        Optional<House> byId = houseRepository.findById(houseId);
        House house = byId.get();
        String communityName = "";
        String buildingNumName = "";
        String unitName = "";
        if (StringUtils.isNotBlank(house.getResidenceCommunityId())) {
            Optional<Community> communityOptional = communityRepository.findById(house.getResidenceCommunityId());
            // 获取小区名称
            if (communityOptional.isPresent()) {
                Community community = communityOptional.get();
                communityName = community.getName();
            }
        }

        if (house.getBuildingNum() != null) {
            // 获取楼栋名称
            Optional<CommunityBuild> communityBuildOptional = communityBuildRepository.findById(house.getBuildingNum());
            if (communityBuildOptional.isPresent()) {
                CommunityBuild communityBuild = communityBuildOptional.get();
                buildingNumName = communityBuild.getName();
            }
        }
        // 获取单元名称
        if (house.getUnit() != null) {
            Optional<CommunityBuildUnit> communityBuildUnitOptional = communityBuildUnitRepository.findById(house.getUnit());
            if (communityBuildUnitOptional.isPresent()) {
                CommunityBuildUnit communityBuildUnit = communityBuildUnitOptional.get();
                unitName = communityBuildUnit.getName();
            }
        }

        String yuan = fenToYuan(String.valueOf(cost));
        String chargeName = billChargeType == 1 ? "租金" : "押金";

        // String sql = "select * from (\n" +
        //         "select a.mobile as mobile ,a.community_house_id as houseId  , a.user_id as userId  from  saas_community_house_user a\n" +
        //         "where   a.status = 1 and  a.account_status = 1 \n" +
        //         "UNION\n" +
        //         "select b.phone as mobile , a.house_id as houseId , a.match_user_id as userId from   saas_house_match_user a \n" +
        //         "LEFT JOIN saas_app_user b on b.id= a.match_user_id\n" +
        //         "where 1=1\n" +
        //         "UNION \n" +
        //         "select b.mobile as mobile , a.house_id as houseId , c.id as userId from   saas_house_match_user a \n" +
        //         "LEFT JOIN saas_house_eligible_apply_cohabit b on b.apply_id= a.apply_id\n" +
        //         "LEFT JOIN saas_app_user c on c.phone = b.mobile \n" +
        //         "where 1=1 and b.mobile is not null \n" +
        //         ") t1 where t1.houseId ='" + houseId + "' ";

        String sql = "select * from (\n" +
                "select a.mobile as mobile ,a.community_house_id as houseId  , a.user_id as userId  from  saas_community_house_user a\n" +
                "where   a.status = 1 and   a.type = 1\n" +
                "UNION\n" +
                "select b.phone as mobile , a.house_id as houseId , a.match_user_id as userId from   saas_house_match_user a\n" +
                "LEFT JOIN saas_app_user b on b.id= a.match_user_id\n" +
                "where 1=1 \n" +
                "UNION " +
                "SELECT c.mobile as mobile ,c.community_house_id as houseId  , c.user_id as userId " +
                "FROM `saas_house` h \n" +
                "LEFT JOIN saas_community_house_user c  on h.id = c.community_house_id \n" +
                "WHERE h.unit_lessee_id is not null \n" +
                "and  c.account_status = 1 and c.status = 1 " +
                ") t1 where t1.houseId ='" + houseId + "' ";
        String where = " and 1=1 ";
        if (StringUtils.isNotBlank(rentBill.getMatchUserId())) {
            where = " or t1.userId = '" + rentBill.getMatchUserId() + "'";
        }

        List<CommunityHouseUser> CommunityHouseUserList = jdbcTemplate.query(sql + where, new BeanPropertyRowMapper<>(CommunityHouseUser.class));

        CommunityHouseUserList = CommunityHouseUserList.stream().collect(Collectors.collectingAndThen(Collectors.toCollection(() -> new TreeSet<>(Comparator.comparing(CommunityHouseUser::getMobile))), ArrayList::new));

        for (CommunityHouseUser communityHouseUser : CommunityHouseUserList) {
            AppUser appUser = appUserRepository.findByPhoneAndTypeAndStatus(communityHouseUser.getMobile(),UserLoginTypeEnum.CONSUMER.getValue(), 1);

            // 生成非公租房租金/押金待办事项
            BacklogItem backlogItem = new BacklogItem();
            backlogItem.setHouseId(houseId);
            backlogItem.setType(BacklogItemTypeEnum.PROPERTY_BILL_RENT.getValue());
            backlogItem.setTitle(chargeName + "账单待缴费");
            backlogItem.setContent(communityName + buildingNumName + "栋" + unitName + "单元" + house.getRoomNumber() + "房，" + chargeName + "账单已生成，请尽快缴费");
            backlogItem.setBusinessStatus("待缴费");
            backlogItem.setHandleStatus(0);
            backlogItem.setOperatorId(operatorId);// 操作人id
            if (appUser != null) {
                backlogItem.setMatchUserId(appUser.getId());
            }
            backlogItem.setPhone(communityHouseUser.getMobile());
            backlogItem.setCreateTime(new Date());
            BacklogItem save = backlogItemRepository.save(backlogItem);

            BacklogItemPropertyRentBill PropertyRentBill = new BacklogItemPropertyRentBill();
            PropertyRentBill.setBacklogItemId(save.getId());
            if (appUser != null) {
                PropertyRentBill.setMatchUserId(appUser.getId());
            }

            if (billChargeType == 1) {
                PropertyRentBill.setRentBillId(rentBillId);// 租金账单id
            } else {
                PropertyRentBill.setDepositBillId(rentBillId);// 押金账单id
            }
            backlogItemPropertyRentBillRepository.save(PropertyRentBill);
            // 账单生成短信
            smsService.sendTemplateSmsWithData(communityHouseUser.getMobile(), smsConfig.getBillTemplateCode(), new String[]{chargeName, yuan});
        }

    }


    /**
     * 非公租房其他费用账单待办事项
     *
     * @param houseId        房屋ID
     * @param rentBillId     账单ID
     * @param billChargeType 账单类型
     * @param cost           金额
     * @param operatorId     操作人
     */
    public void propertyOtherItem(String houseId, Long rentBillId, Integer billChargeType, Integer cost, String operatorId, EstateExpenseBill expenseBill) {
        Optional<House> byId = houseRepository.findById(houseId);
        House house = byId.get();
        String communityName = "";
        String buildingNumName = "";
        String unitName = "";
        if (StringUtils.isNotBlank(house.getResidenceCommunityId())) {
            Optional<Community> communityOptional = communityRepository.findById(house.getResidenceCommunityId());
            // 获取小区名称
            if (communityOptional.isPresent()) {
                Community community = communityOptional.get();
                communityName = community.getName();
            }
        }

        if (house.getBuildingNum() != null) {
            // 获取楼栋名称
            Optional<CommunityBuild> communityBuildOptional = communityBuildRepository.findById(house.getBuildingNum());
            if (communityBuildOptional.isPresent()) {
                CommunityBuild communityBuild = communityBuildOptional.get();
                buildingNumName = communityBuild.getName();
            }
        }
        // 获取单元名称
        if (house.getUnit() != null) {
            Optional<CommunityBuildUnit> communityBuildUnitOptional = communityBuildUnitRepository.findById(house.getUnit());
            if (communityBuildUnitOptional.isPresent()) {
                CommunityBuildUnit communityBuildUnit = communityBuildUnitOptional.get();
                unitName = communityBuildUnit.getName();
            }
        }

        String yuan = fenToYuan(String.valueOf(cost));
        String chargeName = null;
        switch (billChargeType) {  // 1水费 2电费 3物业费 4宽带费 5电视费 6煤气费 7暖气费
            case 1:
                chargeName = "水费";
                break;
            case 2:
                chargeName = "电费";
                break;
            case 3:
                chargeName = "物业费";
                break;
            case 4:
                chargeName = "宽带费";
                break;
            case 5:
                chargeName = "电视费";
                break;
            case 6:
                chargeName = "燃气费";
                break;
            case 7:
                chargeName = "暖气费";
                break;
        }

//        String sql="select * from (\n" +
//                "select a.mobile as mobile ,a.community_house_id as houseId  , a.user_id as userId  from  saas_community_house_user a\n" +
//                "where   a.status = 1 \n" +
//                "UNION\n" +
//                "select b.phone as mobile , a.house_id as houseId , a.match_user_id as userId from   saas_house_match_user a \n" +
//                "LEFT JOIN saas_app_user b on b.id= a.match_user_id\n" +
//                "where 1=1\n" +
//                "UNION \n" +
//                "select b.mobile as mobile , a.house_id as houseId , c.id as userId from   saas_house_match_user a \n" +
//                "LEFT JOIN saas_house_eligible_apply_cohabit b on b.apply_id= a.apply_id\n" +
//                "LEFT JOIN saas_app_user c on c.phone = b.mobile \n" +
//                "where 1=1 and b.mobile is not null \n" +
//                ") t1 where t1.houseId ='"+houseId+"' ";

        /**
         * xin
         * 2022-09-20 13:42
         * 账单审核后只有承租人能收到短信
         */
        String sql = "select * from (\n" +
                "select a.mobile as mobile ,a.community_house_id as houseId  , a.user_id as userId  from  saas_community_house_user a\n" +
                "where   a.status = 1 and   a.type = 1\n" +
                "UNION\n" +
                "select b.phone as mobile , a.house_id as houseId , a.match_user_id as userId from   saas_house_match_user a\n" +
                "LEFT JOIN saas_app_user b on b.id= a.match_user_id\n" +
                "where 1=1  " +
                "UNION SELECT c.mobile as mobile ,c.community_house_id as houseId  , c.user_id as userId " +
                "FROM `saas_house` h LEFT JOIN saas_community_house_user c  on h.id = c.community_house_id " +
                "WHERE h.unit_lessee_id is not null and  c.account_status = 1 and c.status = 1 \n " +
                "" +
                ") t1 where t1.houseId ='" + houseId + "' ";


        String where = " and 1=1 ";
        if (StringUtils.isNotBlank(expenseBill.getMatchUserId())) {
            where = " or t1.userId = '" + expenseBill.getMatchUserId() + "'";
        }

        List<CommunityHouseUser> CommunityHouseUserList = jdbcTemplate.query(sql + where, new BeanPropertyRowMapper<>(CommunityHouseUser.class));

        CommunityHouseUserList = CommunityHouseUserList.stream().collect(Collectors.collectingAndThen(Collectors.toCollection(() -> new TreeSet<>(Comparator.comparing(CommunityHouseUser::getMobile))), ArrayList::new));
        for (CommunityHouseUser communityHouseUser : CommunityHouseUserList) {
            AppUser appUser = appUserRepository.findByPhoneAndTypeAndStatus(communityHouseUser.getMobile(), UserLoginTypeEnum.CONSUMER.getValue(), 1);

            // 非公租房其他费用账单待办事项
            BacklogItem backlogItem = new BacklogItem();
            backlogItem.setHouseId(houseId);
            backlogItem.setType(BacklogItemTypeEnum.PROPERTY_OTHER_BILL.getValue());
            backlogItem.setTitle(chargeName + "账单待缴费");
            backlogItem.setContent(communityName + buildingNumName + "栋" + unitName + "单元" + house.getRoomNumber() + "房，" + chargeName + "账单已生成，请尽快缴费");
            backlogItem.setBusinessStatus("待缴费");
            backlogItem.setHandleStatus(0);
            backlogItem.setOperatorId(operatorId);// 操作人id
            if (appUser != null) {
                backlogItem.setMatchUserId(appUser.getId());
            }
            backlogItem.setPhone(communityHouseUser.getMobile());
            backlogItem.setCreateTime(new Date());
            BacklogItem save = backlogItemRepository.save(backlogItem);

            BacklogItemPropertyOtherBill propertyOtherBill = new BacklogItemPropertyOtherBill();
            propertyOtherBill.setBacklogItemId(save.getId());
            propertyOtherBill.setBillId(rentBillId);

            backlogItemPropertyOtherBillRepository.save(propertyOtherBill);
            // 账单生成短信
            smsService.sendTemplateSmsWithData(communityHouseUser.getMobile(), smsConfig.getBillTemplateCode(), new String[]{chargeName, yuan});

        }

    }
}
